Technical Article

Enable Email Notification to all SQL Jobs

,

Copy the Cursor script and run it. This will enable email notification for the jobs for the operator passed into the script.

USE [msdb]
GO

declare @jobid uniqueidentifier
declare @cmd nvarchar(4000)
DECLARE db_cursor CURSOR FOR  
select job_id from msdb..sysjobs

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @jobid   

WHILE @@FETCH_STATUS = 0   
BEGIN   

--select * from msdb.dbo.sysjobs
--where notify_level_email  2;

--SELECT @jobid
      
  set @cmd = '
   EXEC msdb.dbo.sp_update_job 
@job_id = ''' + convert(nvarchar(2000), @jobid) +''' ,
@notify_level_email=2, 
@notify_level_netsend=2, 
@notify_level_page=2, 
@notify_email_operator_name=N'''''

print @cmd

exec(@cmd);

       FETCH NEXT FROM db_cursor INTO @jobid   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Rate

3 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (3)

You rated this post out of 5. Change rating