Sometimes you may find that people add SQL agent operators, these same operators may at some point need a clear down but what do you do when the operator you want to delete is set as the notification operator for numerous other jobs?
You use the GUI , Right click the operator and click on ‘Delete’ Check the ‘Reassign to:’ box select another Operator to assign ALL of the jobs associated with Mr Obsolete Operator and click ok right?
So that’s what i did , here is an example I have setup to illustrate what SSMS provided me with when i chose to script the Reassign and Delete:
There is a Delete Operator line at the bottom of that script too.
But then I realized that perhaps I do not want ALL of the jobs reassigned to one operator, maybe I do not want some of them to send notifications at all…. doing it this way didn’t offer be any flexibility and I couldn’t be certain which jobs were included in the update without having to query the sysJobs table against the Scripted list.
At a glance (without some adhoc querying) I cannot tell which jobs these are as i only have the job_id’s
Well that’s where this Stored procedure comes in handy but first lets see what error I encountered when deleting to this Operator before even considering reassigning:
Msg 14504, Level 16, State 1, Procedure sp_delete_operator, Line 53 [Batch Start Line 2]
‘NotSoSmoothOperator’ is the fail-safe operator. You must make another operator the fail-safe operator before ‘NotSoSmoothOperator’ can be dropped.
Its an easy fix but for now lets reassign to the correct Operators.
Rather than piece things together we decided to write a Stored Procedure to help us to be selective about what to reassign and what to remove Notifications for altogether as this would come in handy for checking other servers too – And whilst we were at it we thought it would be a good idea to roll in the Fail safe operator check and to provide creation and deletion scripts for the Operators.
Here is a walk-through of the Procedure:
EXEC sp_ChangeAgentJobOperator @OldOperatorName = 'NotSoSmoothOperator', @NewOperatorName = 'SmoothOperator', @DeleteOldOperator = 0
here is the error text:
Msg 50000, Level 11, State 0, Procedure sp_ChangeAgentJobOperator, Line 48 [Batch Start Line 2]
@OldOperatorName Specified is set as the Failsafe Operator – change this in SQL Server Agent > Properties > Alert system. SET @DeleteOldOperator = 0 if you do not want to output the Delete Operator Statement
[NotSoSmoothOperator] is set as the Failsafe Operator, fortunately the error message tells us exactly what we can do to fix this.
For now I will ignore that I want to delete the Operator and focus on just reassigning the jobs , so i SET @DeleteOldOperator = 0 and will come back to removing the Operator later.
Perfect this time i get a list of jobs that are assigned to [NotSoSmoothOperator] with statements to change to [SmoothOperator] and some useful reverts just in case, we can also see the job name here and the notification methods including whether or not the job is Enabled or disabled
You won’t get that from the GUI
But here is the thing – I want to assign some of these jobs to [SmoothOperator] and some of them to [SmootherThanSilkOperator] as they are responsible for different areas so this is where this script has benefit over the GUI – we get to control exactly which jobs are being Reassigned.
I know what you are thinking, why not just do this via the GUI an script out the changes then selectively run the code – well lets take a look at that method:
No Job names, no info on Notification settings, nor any info on whether the job is enabled or disabled
So lets go back to sp_ChangeAgentJobOperator….
I will reassign Jobs for Purchases and SalesAudit to [SmoothOperator]:
EXEC msdb.dbo.sp_update_job @job_id=N'65ACAC62-E113-4E32-ACEB-144CC03A24B3', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'7E2760E7-AD80-48B8-BEA1-4281E18C4F74', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'CF823754-9155-42C3-9091-11B5F8F61F94', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'5008D285-36EB-43CA-9027-7778819C7977', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'5FF05DFC-4973-47F1-8519-DB59D82E5EB3', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'3F823AF4-A113-437F-B4C4-0B31AD6D2A09', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'F16F5FF5-D781-4EFC-A12A-4B78AAFCF426', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'3485005D-FEB3-4A4C-B978-4FECA8CA61E8', @notify_email_operator_name=N'SmoothOperator' EXEC msdb.dbo.sp_update_job @job_id=N'73A19ADB-21BC-4271-AA65-7E8F7AC3CC2B', @notify_email_operator_name=N'SmoothOperator'
Now that we updated those I can re run the Stored Procedure but this time I will Reassign to [SmootherThanSilkOperator]
EXEC sp_ChangeAgentJobOperator @OldOperatorName = 'NotSoSmoothOperator', @NewOperatorName = 'SmootherThanSilkOperator', @CreateNewOperatorIfNotExists = 1, @EmailAddress = 'MyEmail@SQLUndercover.com', @DeleteOldOperator = 0
Operator does not exist but we can run the output to create then re run the Procedure
Here is my new list:
So this time i need to Reassign Jobs 1 -4 but for number 5 this no longer needs a notification of any kind, first lets reassign the first 4 maintenance jobs:
EXEC msdb.dbo.sp_update_job @job_id=N'49870ABB-B1D9-44F1-AB9C-FA8D77143565', @notify_email_operator_name=N'SmootherThanSilkOperator' EXEC msdb.dbo.sp_update_job @job_id=N'C3AA2902-2CB8-4E4A-8E94-2A03F024494D', @notify_email_operator_name=N'SmootherThanSilkOperator' EXEC msdb.dbo.sp_update_job @job_id=N'1E318980-F332-4239-A686-3F2643B310FF', @notify_email_operator_name=N'SmootherThanSilkOperator' EXEC msdb.dbo.sp_update_job @job_id=N'B7EA707A-6333-4D8C-B189-7BF44DE7F8C2', @notify_email_operator_name=N'SmootherThanSilkOperator'
Run the Procedure again but this time we will SET @NewOperatorName to NULL as this will remove the notifications:
EXEC sp_ChangeAgentJobOperator @OldOperatorName = 'NotSoSmoothOperator', @NewOperatorName = 'SmootherThanSilkOperator', @DeleteOldOperator = 1
Copy and paste the contents of ChangeToNewOperator and Execute:
EXEC msdb.dbo.sp_update_job @job_id=N'F1EA3D6D-D650-462F-91C4-3A07CC197FA3', @notify_level_netsend=0, @notify_level_page=0, @notify_level_email=0, @notify_email_operator_name=N''
A quick look at the job will show that the Notifications have now been removed:
So this leaves us one last thing to do and thats remove this Operator [NotSoSmoothOperator] but we need to either disable the Fail safe or reassign, I will reassign using the Gui:
SQL Server Agent > Properties > Alert system
Reassigned to [SmootherThanSilkOperator] as highlighted
Now I can just simply delete from the Operators Folder OR run the Procedure with @DeleteOldOperator = 1 like below to produce the statement:
EXEC sp_ChangeAgentJobOperator @OldOperatorName = 'NotSoSmoothOperator', @NewOperatorName = 'SmootherThanSilkOperator', @DeleteOldOperator = 1
Copy and paste the contents of ChangeToNewOperator and Execute:
EXEC msdb.dbo.sp_delete_operator @name=N'NotSoSmoothOperator'
Command(s) completed successfully
All done.
Warning – this script uses XP_INSTANCE_REGREAD which is an undocumented Extended Stored procedure.
Here is the code:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ChangeAgentJobOperator]
(
@OldOperatorName NVARCHAR(128),
@NewOperatorName NVARCHAR(128),
@CreateNewOperatorIfNotExists BIT = 0,
@EmailAddress NVARCHAR(128) = NULL,
@DeleteOldOperator BIT = 0
)
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT Name FROM msdb.dbo.sysoperators WHERE name = @OldOperatorName)
BEGIN
IF EXISTS (SELECT Name FROM msdb.dbo.sysoperators WHERE name = @NewOperatorName) OR @NewOperatorName IS NULL
BEGIN
IF OBJECT_ID('TempDB..#AgentJobs') IS NOT NULL
DROP TABLE #AgentJobs;
CREATE TABLE #AgentJobs
(
job_id uniqueidentifier NOT NULL
,name nvarchar(128) NOT NULL
,notify_level_email int NOT NULL
,notify_level_netsend int NOT NULL
,notify_level_page int NOT NULL
);
INSERT INTO #AgentJobs
EXEC msdb.dbo.sp_help_operator_jobs @Operator_name= @OldOperatorName;
IF @DeleteOldOperator = 1
BEGIN
DECLARE @FailSafeOperator NVARCHAR(128)
EXEC SYS.XP_INSTANCE_REGREAD N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator',
@FailSafeOperator output
IF (@FailSafeOperator != @OldOperatorName OR @FailSafeOperator IS NULL)
BEGIN
INSERT INTO #AgentJobs (job_id,name,notify_level_email,notify_level_netsend,notify_level_page)
VALUES ('00000000-0000-0000-0000-000000000000','','','','')
END
ELSE
BEGIN
RAISERROR('@OldOperatorName Specified is set as the Failsafe Operator - change this in SQL Server Agent > Properties > Alert system. SET @DeleteOldOperator = 0 if you do not want to output the Delete Operator Statement',11,0)
END
END
SELECT #AgentJobs.name AS JobName,
CASE WHEN @NewOperatorName IS NULL THEN
'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(#AgentJobs.Job_id AS VARCHAR(36))+''',
@notify_level_netsend=0,
@notify_level_page=0,
@notify_level_email=0,
@notify_email_operator_name=N''''' + CHAR(13)+CHAR(10)
WHEN @NewOperatorName IS NOT NULL THEN
'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(#AgentJobs.Job_id AS VARCHAR(36))+''',
@notify_email_operator_name=N'''+@NewOperatorName+'''' + CHAR(13)+CHAR(10)
END AS ChangeToNewOperator,
'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(#AgentJobs.Job_id AS VARCHAR(36))+''',
@notify_email_operator_name=N'''+@OldOperatorName+'''' + CHAR(13)+CHAR(10) AS RevertBackToOldOperator,
CASE
#AgentJobs.Notify_Level_email
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On success'
WHEN 2 THEN 'On failure'
WHEN 3 THEN 'Always'
END AS EmailNotification,
CASE
#AgentJobs.Notify_Level_netsend
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On success'
WHEN 2 THEN 'On failure'
WHEN 3 THEN 'Always'
END AS NetSendNotification,
CASE
#AgentJobs.Notify_Level_page
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On success'
WHEN 2 THEN 'On failure'
WHEN 3 THEN 'Always'
END AS PageNotification,
CAST(sysjobs.[Enabled] AS CHAR(1)) AS [Enabled]
FROM #AgentJobs
INNER JOIN msdb..sysjobs ON #AgentJobs.job_id = sysjobs.job_id
WHERE #AgentJobs.job_id != '00000000-0000-0000-0000-000000000000'
UNION ALL
SELECT
'',
CASE WHEN @DeleteOldOperator = 1 THEN '--EXEC msdb.dbo.sp_delete_operator @name=N'''+@OldOperatorName+''''
ELSE ''
END,
'',
'',
'',
'',
''
FROM #AgentJobs
WHERE #AgentJobs.job_id = '00000000-0000-0000-0000-000000000000'
ORDER BY JobName ASC
END
ELSE IF @NewOperatorName IS NOT NULL
BEGIN
RAISERROR('@NewOperatorName Specified does not exist SET @CreateNewOperatorIfNotExists = 1 or create via the Operators folder',1,0)
IF @CreateNewOperatorIfNotExists = 1 AND @NewOperatorName IS NOT NULL
BEGIN
SELECT '/** Run the following Add Operator command then run the procedure again to see the list of agent jobs associated with the Old Operator **/'
AS Create_NewOperator
UNION ALL
SELECT 'EXEC msdb.dbo.sp_add_operator @name=N'''+@NewOperatorName+''',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@category_name=N''[Uncategorized]''
'+CASE WHEN @EmailAddress IS NOT NULL THEN ',@email_address=N'''+@EmailAddress+''''
ELSE ''
END
AS Create_NewOperator
END
END
END
ELSE
BEGIN
RAISERROR('@OldOperatorName Specified does not exist',1,0)
END
END
GO
Here is a run down of the Parameters:
@OldOperatorName NVARCHAR(128) – No Default:
Specify the Name of the Operator that you want to Reassign jobs from.
@NewOperatorName NVARCHAR(128) No Default:
Specify the Name of the Operator that you want to Reassign jobs to from @OldOperatorName.
@CreateNewOperatorIfNotExists BIT – Default 0:
Off by default , when enabled if the @NewOperatorName specified does not exist then a Creation script will be Output with default settings.
@EmailAddress NVARCHAR(128) – Default NULL:
Specify an email for @NewOperatorName when @CreateNewOperatorIfNotExists is enabled.
@DeleteOldOperator BIT – Default 0:
Off by default , when enabled if the @OldOperatorName specified exists then a Deletion script will be Output.
Thanks for reading 🙂
SQL Undercover 
Leave a Reply