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!
Best Answer
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.