I wanted to standardize job error notifications, so created an new operator called ‘DBA’ (with multiple email addresses).
This code semi-automates the process of updating all the jobs by listing them, along with the code needed to change them …
/* ChangeNotifications.sql */ SELECT J.[name] JobName, O.[name] OperatorName, O.email_address Email, 'EXEC msdb.dbo.sp_update_job @job_name = N''' + J.[name] + ''', @notify_level_email=2, @notify_email_operator_name = N''DBA''' CommandToChangeIt FROM msdb..sysjobs J LEFT JOIN msdb..sysoperators O ON O.id = J.notify_email_operator_id ORDER BY Email, JobName;