Advertisements
Home > SQL Errors > SQL Error – The specified ‘@notify_email_operator_name’ is invalid (valid values are returned by sp_help_operator)

SQL Error – The specified ‘@notify_email_operator_name’ is invalid (valid values are returned by sp_help_operator)


 
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
 


Advertisements
Categories: SQL Errors Tags:
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: