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