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’.
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.”
Reason/Resolution:
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