QUOTED_IDENTIFIER Gotcha!

This one is new for me. It might be something you have to consider all the time. I do not.

Stored procedures in SQL may have defaults for certain options set only at creation. So you can't override them or even alter them. You have to drop and recreate them if those options have an affect on your query.

This probably doesn't affect 99% of your queries. It doesn't affect mine too much. Today I had to so a bit of XML in my stored procedure though. It worked in dev fine because of course my options are set differently than in another server. When I gave the script to someone else to try... whammo!

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

What the hell does that mean? Is this Office Space? PC LOAD LETTER!

No it's not a movie. This is real life and SQL has this cool table called sys.sql_modules. It can store options that you have set when you created that bloody stored procedure that isn't working now.

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

In this example our stored procedure is called sproc1. The columns we can look for are:

• object_id
• definition
• uses_ansi_nulls
• uses_quoted_identifier
• is_schema_bound
• uses_database_collation
• is_recompiled
• null_on_null_input
• execute_as_principal_id

For this example our results prove that we do have a problem.

name uses_quoted_identifier
---- ---------------
sproc1 0
sproc2 1

So how do we fix this. We can't do an alter. We have to recreate the stored procedure. Yep, what a pain in the butt.  It's because of the darn XML and STUFF() that we are doing to put some rows all together into a delimited string.  Please don't focus on that.. it's the QUOTED_IDENTIFIER ON that is the bug.

--drop the sp and recreate with the correct set options.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc1]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sproc1]

GO

-- important

SET ANSI_NULLS ON
GO

-- important for xml queries

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sproc1]
AS
BEGIN

-- important for mapping in Entity Framework
SET FMTONLY OFF

-- SELECT an existing row from the table.

CREATE TABLE #Things
(
[Id] INT,
[Description] VARCHAR (200),
[Date] DATETIME NOT NULL
)

INSERT INTO #Things(Id, Description, Date)
SELECT ID, Description, Date
FROM TableA
INNER JOIN TableB ON TableB.ID = TableA.ID
WHERE Description IS NOT NULL

SELECT a.Id,
STUFF((
SELECT ', ' + b.Description
FROM #TableA b
WHERE (b.Id = a.Id)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS Things 
FROM #Things a
GROUP BY Id

DROP TABLE #Things

END

GO