とりあえず、沢山あるログシッピングのLSCopyとLSRestoreジョブの失敗時のアラートを飛ばしたい。
DBメールの設定と、オペレーターの設定は先に済ませておく。
[code]
USE msdb
GO
declare @job_copy nvarchar(50)
declare @job_restore nvarchar(50)
DECLARE C1_LSCOPY CURSOR FOR
SELECT name FROM dbo.sysjobs where name LIKE ‘LSCopy%’
DECLARE C1_LSRESTORE CURSOR FOR
SELECT name FROM dbo.sysjobs where name LIKE ‘LSRestore%’
OPEN C1_LSCOPY
FETCH NEXT FROM C1_LSCOPY INTO @job_copy
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC dbo.sp_update_job @job_name = @job_copy,
@notify_level_email=2,
@notify_email_operator_name=N’Admin’;
FETCH NEXT FROM C1_LSCOPY INTO @job_copy
END
OPEN C1_LSRESTORE
FETCH NEXT FROM C1_LSRESTORE INTO @job_restore
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC dbo.sp_update_job @job_name = @job_restore,
@notify_level_email=2,
@notify_email_operator_name=N’Admin’;
FETCH NEXT FROM C1_LSRESTORE INTO @job_restore
END
CLOSE C1_LSCOPY
CLOSE C1_LSRESTORE
DEALLOCATE C1_LSCOPY
DEALLOCATE C1_LSRESTORE
GO
[/code]