If you have a SQL Agent job that’s failing, but the query succeeds when you run it manually in SQL Management Studio, it’s possibly a difference between the SQL Server default connection settings and those SSMS uses as the default. In my case, I was inserting from a stored procedure and received the following error:
[SQLSTATE 42000] (Error 1934) INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
To view the default connection options for your SQL Server instance, right-click on your instance in SSMS and select “Properties”, and then browse to “Connections” – you’ll see something like the following list (on my server, none of these are enabled by default for new connections):
To compare it to the default settings for your installation of SSMS, click on “Tools” and then “Options…”, and then browse to “Query Execution” -> “SQL Server” and view the settings under both “Advanced” and ANSI” (In my case, SSMS was enabling a number of settings by default for my connections that SQL Agent jobs wouldn’t have enabled):
In my case, I just added an explicit “SET QUOTED_IDENTIFIER ON” to the beginning of the script in my SQL Agent job and it resolved the error message. Hope this helps you too!