Change Job notification Operators

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;