TryCatchFinally.net Some SQL, some .NET, and whatever else

2Mar/160

SQL Agent job fails with QUOTED_INDENTIFIER error

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):

Server Defaults

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):

SSMS Results ANSI

SSMS Results Advanced

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!