Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...".

The question is, why does this problem appear in production, and not in any of our testing environments? Checking DBCC USEROPTIONS, the results are exactly the same across all databases. Also, the script itself contains no quotes at all. It simply rebuilds a few indexes and updates a few statistics with fullscan.

Using the exact same job on any other database, I can set QUOTED_IDENTIFIER ON, or OFF, or just not set it at all and let it run on connection defaults. And it'll always work regardless. But in production, it appears it either has to be set ON or OFF.

I'm asking this because time-wise I can't afford to test this once a day, I need to know why this error is happening and how to predict which setting is needed and when. The only visible difference between the test and production databases is collation, but even creating a testing database to match the collation in production, the Job still works regardless of the QUOTED_IDENTIFIER setting.

Thanks!

share|improve this question
    
"When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure." Source. Does the job use any procedures? If so does sys.sql_modules show the same setting on all servers? –  Martin Smith Nov 5 '13 at 11:07
    
Nope, the job is literally one line of comments /* comment */ followed by a number of straight, hard-coded index rebuilds and stats updates. It doesn't refer to any procedures, nor has any dynamic or parameterized components of any kind. –  Kahn Nov 5 '13 at 11:16
add comment

2 Answers

I just ran into this same issue. By default the first statement in a job step is to SET QUOTED_IDENTIFIER OFF - you can see this in a Profiler trace. The solution is to add a line at the beginning of your code to SET QUOTED_IDENTIFIER ON.

As to your script not containing quotes - I believe when creating or rebuilding certain types of indexes that setting is required is to be on.

Here's a couple links:

http://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off

http://blogs.msdn.com/b/sqlagent/archive/2010/11/05/sql-agent-issue-with-identifiers-delimited-by-double-quotation-marks.aspx

share|improve this answer
add comment

I discovered that the SQL Agent job sets the database options based on the compatibility level of the database and this can override the database options that have been set.

i.e. Database has database options of SET QUOTED_IDENTIFIER ON however the compatibiliy level of the database is set to SQL 2000 so that the SQL Job fails (i.e. when attempting to UPDATE rows in table that has filtered index on it).

Switch the compatibility level to SQL 2008 and then the SQL job will succeed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.