How to find of the QUOTED_IDENTIFIER used for stored procedure

02 February,2017 by Jack Vamvas

Question: When executing a stored procedure I received an error message

 

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.

 

I’ve checked my SSMS settings which indicated SET QUOTED IFDENTIFIER ON, so I’m slightly confused about why this is not applying to the stored procedure I’m executing. Is there a method to present the QUOTED_IDENTIFIER value used when the stored procedure was created ?

 

Answer: It's worth understanding there is a difference between executing a batch statement through SSMS and executing a stored procedure .

Executing through the SSMS will pick up the value set at the SSMS level. Unless you override the setting by using SET QUOTED_IDENTIFIER OFF | ON at the top of your sql batch statement

Set_quoted_identifier

These settings won’t apply if you are executing a stored procedure , for example : exec mySP1 . It uses the QUOTED_IDENTIFIER setting used when the stored procedure was created . How can you find out the QUOTED_IDENTIFIER value used when the stored procedure was created. ?

This query using sys.sql_modules view the will return the QUOTED_IDENTIFIER value used when the stored was created.

 

 

SELECT name = OBJECT_NAME([object_id]), ,uses_quoted_identifier
  FROM sys.sql_modules
  WHERE OBJECT_NAME([object_id]) IN (N',mysp1', N'mysp2');

If you want to change the QUOTED_IDENTIFIER value , you can recreate the stored procedure but this time apply SET QUOTED_IDENTIFIER ON

Read More  

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

SQL Server - Last time a stored procedure was executed

 


Author: Jack Vamvas (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 How to find of the QUOTED_IDENTIFIER used for stored procedure


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