Friday, June 2, 2017

Changing Notification Operator for Multiple or all SQL Agent Jobs

--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 = 'SQLOperDBA'

--Update the affected rows with new operator_id:
UPDATE msdb.dbo.sysjobs
SET notify_email_operator_id = @operator_id
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.sysoperators O
ON msdb.dbo.sysjobs.notify_email_operator_id = O.[id]
--check where clause , if not it is going to update all jobs

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...