Home > SQL Server 2008 > Rebuild Job failure issue at SQL server 2008 R2 environment incorrect settings: ‘QUOTED_IDENTIFIER’

Rebuild Job failure issue at SQL server 2008 R2 environment incorrect settings: ‘QUOTED_IDENTIFIER’


We have received the below error:

 

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.

 

 

Resolution:

 

Please pasted the below three lines on the rebuild job step

 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

Go

SET ARITHABORT ON

 

 

Explanation:

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure, Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.

We can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier  FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')
Advertisements
Categories: SQL Server 2008
  1. January 27, 2013 at 6:01 pm

    After looking at a few of the blog posts on your blog, I seriously like your way of blogging. I added it to my bookmark site list and will be checking back soon. Please visit my web site as well and let me know how you feel.

  2. February 10, 2013 at 7:17 am

    An intriguing discussion is definitely worth comment. I think that you need to publish more about this subject, it may not be a taboo matter but generally people do not speak about such issues. To the next! Cheers!!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: