Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

SQL Server – ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF

03 December,2012 by Tom Collins

Question: A regular index management job failed  and logged this  message. The index management job is run through the SQL Server Agent. :

 

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 query notifications and/or xml data type methods. [SQLSTATE 42000]

 

How can I fix this issue?

Answer:   The underlying reason  is that SET 'QUOTED_IDENTIFIER'  is OFF. This index is relying on an indexed view , which means the error will occur.  Adding the  SET 'QUOTED_IDENTIFIER' to ON will fix the problem

According to SQL Server BOL “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail”

 You may notice a disparity between executing the code through SSMS and SQL Server Agent.  If you  execute the code through SSMS , it will  most likely work – as by default SET 'QUOTED_IDENTIFIER' is set to ON.   By default , the SQL Server Agent does not set SET ‘QUOTED_IDENTIFIER’ or  ‘ARITHABORT’ .   

 To overcome the problem on SQL Server Agent , set the SET QUOTED_IDENTIFIER at the top of the script.

If you want to list the computed columns on a SQL Server table read: How to find computed columns on a SQL Server table

Read More

 SQL Server - Send sql server agent job history through email ...

SQL Agent Jobs – Schedule in seconds - SQL Server DBA

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer