I encountered the following error when I was enabling the CDC for the db1.dbo.pat_table
Could not update the metadata that indicates table [dbo].[pat_Table] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database db1 to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.
After researching around I understood that the above error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers
I solved the issue as follows -
select @@SERVERNAME --oldname
select SERVERPROPERTY('ServerName') --defaultname
--1 drop the old servername
exec sp_dropserver 'oldservername'
--2 add new servername
exec sp_addserver 'newservername', 'local'
--3 see changes
exec sp_helpserver
Tuesday, April 26, 2016
Monitor disk space
/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/
use MyAdminDB
GO
declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
SET NOCOUNT ON
DECLARE @list nvarchar(2000) = '';
WITH core AS (
SELECT DISTINCT
s.volume_mount_point [Drive],
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
FROM
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
FROM core
WHERE AvailableMBs < @threshold
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END
RETURN 0
GO
use MyAdminDB
GO
declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
SET NOCOUNT ON
DECLARE @list nvarchar(2000) = '';
WITH core AS (
SELECT DISTINCT
s.volume_mount_point [Drive],
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
FROM
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
FROM core
WHERE AvailableMBs < @threshold
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END
RETURN 0
GO
Thursday, April 21, 2016
dbmail: sending html table in the email
Declare
@xml nvarchar(max),
@body nvarchar(max)
SET @xml = CAST(( SELECT clientcode AS 'td','',right(FilenameStr,charindex('\',reverse(FilenameStr),5)-1) as 'td','', cast(importstartDate as varchar) as 'td','',cast(ImportEndDate as varchar) as 'td'
,'', totnum as 'td', '', totdups as 'td','', totloaded as 'td','', totfail as 'td', '', totskip as 'td' FROM #fileList order by HospitalCode, importstartDate FOR XML PATH('tr'),ELEMENTS) as nvarchar(MAX))
--select @xml
SET @body ='<html><body><H3> files list for date ' + @sqllasjobrundate + '-' + convert(varchar(8),getdate(),112) + ' </H3>
<table border = 1>
<tr>
<td> Hospital code</td> <td> Fine name </td> <td> Import start date </td></td> <td> Import end date </td>
<td> Total count in file </td><td> Total duplicates </td><td> Total imported </td><td> Total failed validation </td><td> Total skipped </td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail@mycompany.com', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'mail1@mycompany.com', -- replace with your email address
@subject = 'file log Report ' ;
end
Subscribe to:
Posts (Atom)