Advertisements

Archive

Archive for June, 2014

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

June 24, 2014 Leave a comment

 
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:

How to convert ‘20140620’ date to ‘DD-MM-YYYY’ format – MSDN TSQL forum

June 12, 2014 Leave a comment

–> Question:

I have data for date fields like “20140620”(YYYYMMDD) while inserting into the table it should be in the format “DD-MM-YYYY

Can any one please help me to achive this.
 

–> My Response:

SELECT FORMAT(CAST('20140620' as DATE), 'dd-MM-yyyy', 'en-US'), CAST('20140620' as DATE)

… this will work with SQL Server 2012 and above.
 

–> Other Responses:

-- by Olaf Helper
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY

-- by Latheesh NK
SELECT CONVERT(VARCHAR(10), '20140620', 105)

 

Ref Link.