SQL Server Agent: QUOTED_IDENTIFIER 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!
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:

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