UNDERCOVER TOOLBOX: sp_ChangeAgentJobOperator – Scripting out change of notification operator , Deleting and/or Creating.

2017-09-13 22_59_30-Presentation1 - PowerPoint

 

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:

 

2017-09-13 21_28_15-SQLQuery11.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (59)) - Microsoft SQL S
And the list goes on…

 

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

 

2017-09-13 20_27_51-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

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.

 

2017-09-13 20_44_23-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

 

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:

 

2017-09-13 20_35_07-SQLQuery5.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (53)) - Microsoft SQL Se

 

 

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

 

2017-09-13 20_51_31-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

 

Operator does not exist but we can run the output to create then re run the Procedure

Here is my new list:

 

2017-09-13 20_52_20-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

 

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

 

2017-09-13 20_58_52-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

 

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:

 

2017-09-13 20_59_56-Job Properties - _Maintenance5

 

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

 

2017-09-13 21_02_21-SQL Server Agent Properties - CATACLYSM_SQL01

 

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

 

2017-09-13 21_04_22-SQLQuery4.sql - CATACLYSM_SQL01.master (CATACLYSM_adest (54))_ - Microsoft SQL S

 

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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: