SET QUOTED_IDENTIFIER ON within the stored procedure is not working-CodePudding

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.

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

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.

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.

Thank you in advance.

CodePudding user response：

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

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