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!

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

Why ask?

No trackbacks yet.