Changing Notification Operator for Multiple SQL Agent Jobs
Recently I found myself in a situation where I needed to make a mass change to the operator that was to be notified in the event of a failure. This change had to occur across all SQL instances under my administrative control. Do I need to perform a repetitive process for hundreds of SQL Agent Jobs? Heck no. I just need to have a basic understanding of T/SQL and an understanding of the underlying schema of the msdb database. After that it’s a matter of a dozen lines of code (even less for you since I’m giving you the code) and perhaps 15 minutes of time to run and validate.
The first step is to ensure the new operator exists in the msdb database. “What’s the msdb database,” you say? That is the database that Microsoft SQL Server utilizes to store all backup information as well as metadata for the jobs, the operators, and alerts all associated with the SQL Agent service. The information concerning operators is stored in the msdb.dbo.sysoperators table; the primary key being the id column.
After verifying the new operator exists in msdb it’s simply a matter of an update statement against the msdb.dbo.sysjobs table (the table that stores job info). We’re interested in changing the notify_email_operator_id column in msdb.dbo.sysjobs to the new operator id. This is done through a join between both tables where msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id. In the code below I’m using ‘DBA_Group’ as my operator name. Be sure to change that to fit your exact need:
--Check to see if operator exists currently:
SELECT [name], [id], [enabled]
FROM msdb.dbo.sysoperators
ORDER BY [name];
--Declare variables and set values:
DECLARE @operator_id int
SELECT @operator_id = [id]
FROM msdb.dbo.sysoperators
WHERE name = 'DBA_Group'
--Update the affected rows with new operator_id:
UPDATE msdb.dbo.sysjobs
SET notify_email_operator_id = @operatorid
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.sysoperators O
ON msdb.dbo.sysjobs.notify_email_operator_id = O.[id]
WHERE O.[id] <> @operator_id;
If you want to accomplish this against multiple instances you can create a server group in Registered Servers within SQL Server Management Studio. You can then right-click on the server group and select New Query. Paste the command into the the query window and the code will run against each SQL Server instance in the Registered Servers group that is online and accessible at the time you execute the query.
Easy-Peasy. Now tell your boss it’s going to take you all week to do this when the issue arises for you and make out like a rockstar when you get it done in minutes….