sql-serverquoted-identifier

SET QUOTED_IDENTIFIER ON within the stored procedure is not working

I am using FOR XML PATH in my stored procedure and so that I need to have QUOTED_IDENTIFIER set to ON. I have it as a first statement of my stored procedure.

SET QUOTED_IDENTIFIER ON;

This is working fine all the times except I restore my database first time.

Just after restoring the database, I checked sp_helptext <SPName> and the stored procedure seems fine.

sp_helptext

However when I browse my stored procedure from the Object Explorer and click on "Modify", it shows this:

Modify

When I tried executing the stored procedure using EXEC <SP_Name> it throws an error

SELECT 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.

enter image description here

Can someone guide me why SQL Server is adding SET QUOTED_IDENTIFIER OFF on its own? How to get rid of it? or How can we overwrite it from within the stored procedure?

If I remove SET QUOTED_IDENTIFIER OFF from the top, re-execute/run it then it will work fine.

My problem is - We have automated process which runs migrations/create database on every build so I can not remove it manually all the times. I also can not set it on the database level.

I checked the database setting for that and its set to false but that should not make any difference right? As I have it specifically SET To ON inside my stored procedure.

enter image description here

Thank you in advance.

Solution

    1. The QUOTED_IDENTIFIER database setting is irrelevant. It's only a default, every client driver sets QUOTED_IDENTIFIER on connecting and overrides the database default.

    2. The SET QUOTED_IDENTIFIER inside the stored procedure is irrelevant; remove it.

    SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static Transact-SQL.

    SET QUOTED_IDENTIFIER

    1. The setting for QUOTED_IDENTIFIER which was in force for the batch creating the stored procedure will be stored in the catalog, and used by SSMS to script the stored procedure. It does not matter what the current session setting is, or the database default is, or what the SSMS query options are. If it was created with QUOTED_IDENTIFIER ON it will be scripted with QUOTED_IDENTIFIER ON. You can see the stored setting here in sys.sql_modules.

    eg

    select uses_quoted_identifier
from sys.sql_modules
where object_id = object_id('MyProc')

    So,

    However when I browse my stored procedure from the Object Explorer and click on "Modify", it [is scripted with QUOTED_IDENTIFER OFF].

    If the setting in sys.sql_modules different than the setting in the generated script, that would be a bug in SSMS/SMO.

    And this:

    enter image description here

    means that the stored procedure was created with QUOTED_IDENTIFIER OFF. As you see the session setting for the session invoking the stored procedure is irrelevant.

    When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

    SET QUOTED_IDENTIFIER

    This stored procedure's setting also controls the QUOTED_IDENTIFIER setting for dynamic SQL inside the stored procedure. But inside dynamic SQL you can change the setting.

    And so yes

    My problem is - We have automated process which runs migrations/create database on every build

    This process is broken, as it's creating your proc with QUOTED_IDENTIFIER OFF. If you can't fix it you can work around it by pushing your TSQL into a dynamic batch, and setting QUOTED_IDENTIFIER ON in the dynamic SQL. eg

    set quoted_identifier off
go
create or alter procedure foo
as
begin
  exec ('set quoted_identifier on; select * from "sys"."objects"')

end
go

exec foo --suceeds

    Also you can make your stored procedure create script depend on QUOTED_IDENTIFIER ON so you can't possibly create it with QUOTED_IDENTIFIER OFF, eg

    set quoted_identifier off
go
create or alter procedure foo
as
begin
  select * from "sys"."objects" 
end

    fails with

    Msg 102, Level 15, State 1, Procedure foo, Line 4 [Batch Start Line 2]
Incorrect syntax near 'sys'.