SQL Agent Issue with identifiers delimited by double quotation marks

The issue is related to default settings for quoted_identifier. In SSMS query window the default settings for quoted_identifier is ON. It means that the following code will work:


SELECT @Days = 5 

SELECT 'Test', DateAdd("dd",@Days,GetDate())

But if you copy and paste this T-SQL statement to SQL Agent step (T-SQL) it will fail with the following error:

 Invalid parameter 1 specified for dateadd. [SQLSTATE 42000] (Error 1023).  The step failed.

By default SQL Agent doesn't enforce quoted_identifier settings and allows to user to specify the correct behavior for the TSQL step. It can be debated if it is a right or wrong behavior but its how it works. So, to fix above T-SQL query you need explicitly call quoted_identifier



SELECT @Days = 5

SELECT 'Test', DateAdd("dd", @Days, GetDate())


This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified at

Comments (0)

Skip to main content