Tuesday, November 29, 2011

DBREINDEXING Fails for one particular DB with error : “SET options have incorrect settings: 'QUOTED_IDENTIFIER'.”


DBREINDEXING job which performs both ONLINE and OFFLINE re-indexing operation running fine on other servers but failing in one server with below error:

“ALTER INDEX 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. [SQLSTATE 42000] (Error 1934).  The step failed.”

As per microsoft if we have index on a computed column and when trying to perform re-indexing we need to SET 'QUOTED_IDENTIFIER' to ON.

So I just added the statement ‘SET QUOTED_IDENTIFIER ON ‘ before re-indexing code execution. This resolved my issue and the job went fine without issue.
Here the interesting thing I noticed is when I am executing code through SSMS it executes successfully but when I’m trying to run the same code using a SQL Scheduled job the step fails. Once after adding the command my job is running fine. This is because by default SQL Agent does not set 'QUOTED_IDENTIFIER' or ‘ARITHABORT’.

No comments:

Post a Comment