Monday, March 11, 2013

Enable email to operator for all jobs using TSQL

There were a bunch of jobs that I needed to enable email notifications in case of a failure. I came up with this quick and dirty way:

DECLARE @job_id1 nvarchar(200)
DECLARE updateOperator CURSOR FOR 
select job_id from msdb..sysjobs WHERE enabled = 1

OPEN updateOperator
FETCH NEXT FROM updateOperator INTO @job_id1

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id= @job_id1, 
  @notify_level_email=2, 
  @notify_level_netsend=2, 
  @notify_level_page=2, 
  @notify_email_operator_name=N'Master-DBA'

    FETCH NEXT FROM updateOperator INTO @job_id1
    END

CLOSE updateOperator
DEALLOCATE updateOperator