SQL with Manoj

 
Today while creating some SQL Jobs I faced following error:

Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 245
The specified ‘@notify_email_operator_name’ is invalid (valid values are returned by sp_help_operator).

The Job’s Script was created from PROD environment and as the same operator was not available on my DEV server, executing same script on DEV environment was throwing this error. So now how to fix this error?
 

–> One method is to check if an Operator already exist n DEV server and use it in your SQL Job Script:

USE [msdb]
GO
EXEC sp_help_operator
GO

–> If the Operator does not exist then create a new Operator by using following DDL query:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator 
	@name = N'OperatorName',
	@enabled = 1,
	@email_address = N'SupportEmail@DomainName.com',
	@category_name = N'Uncategorized'
GO

… and use the Operator name in the Script that you got from PROD server, and update the below part of the DDL Script:

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Acquire_Data_Source1', 
	@enabled=1, 
	@notify_level_eventlog=0, 
	@notify_level_email=2, 
	@notify_level_netsend=0, 
	@notify_level_page=0, 
	@delete_level=0, 
	@description=N'No description available.', 
	@category_name=N'ESBIAcquisition', 
	@owner_login_name=N'DOMAIN\Account', 
	@notify_email_operator_name='Operatorname', -- <-- Here
	@job_id=@jobId OUTPUT

–> If you are not sure about the Operator name and just testing the Jobs in DEV or Test environment and don’t any email alerts then just assign NULL instead of the operator name like:

@notify_email_operator_name=NULL