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

Question

Whats wrong with this statement? why it gives below error message

Error Message:

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.

Statement

  SELECT  ISNULL(STUFF((SELECT ', ' + u.TITLE + ' ' + u.FirstNAME  + ' ' + LastNAME   [text()]
                                         FROM table1 c 
                                         INNER JOIN table2 u ON c.user = u.user
                                         AND c.Task = @task 
                                         AND c.Type = 'Assign'
                                         FOR XML PATH(''), TYPE)
                                        .value('.','NVARCHAR(MAX)'),1,2,' ')
                                 ,'') UNAMESLIST

Answer 1

As documented in SET QUOTED_IDENTIFIER (Transact-SQL)

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

A simple test

set quoted_identifier off


DECLARE @xmlRecords XML
SET     @xmlRecords = '<records><record orderId="1" refCode="1234"></record></records>'


SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId
FROM    @xmlRecords.nodes('/records/record') records(record)

Msg 1934, Level 16, State 1, Line 8 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 query notifications and/or xml data type methods.

You are using FOR XML PATH

SET QUOTED_IDENTIFIER off

DECLARE @T TABLE (id VARCHAR(5),col1 XML)

INSERT INTO @t (id,col1) VALUES ('1','<node1>one</node1>')

SELECT ISNULL(STUFF((
                SELECT ', ' + id
                FROM @t
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' '), '') UNAMESLIST

Msg 1934, Level 16, State 1, Line 8 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 query notifications and/or xml data type methods.

Answer 2

Check results from command:

DBCC useroptions

There are other very important settings to observe here too:

language    us_english
dateformat  mdy
datefirst   7

Here in Australia am using settings as per:

language    British
dateformat  dmy
datefirst   1

Strangely, my process worked on a dev level DB but not SIT level DB, I resolved this issue by adding this line to my stored proc that performs the XML import:

SET QUOTED_IDENTIFIER ON

I also found that this has to be added above the CREATE PROCEDURE statement ! i.e. as per this example:

SET ANSI_WARNINGS ON
GO
SET ANSI_PADDING ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.P427007ImportAHSAHospitalDetails
    @Path       VARCHAR(255),
    @FileName   VARCHAR(255),
    @OperatorID VARCHAR(10),
    @BranchID   VARCHAR(10),
    @TillID     VARCHAR(10)
AS 
BEGIN

    SET ANSI_WARNINGS ON
    SET ANSI_PADDING ON
    SET QUOTED_IDENTIFIER ON    

    DECLARE @DebugModeFlag CHAR 
    SELECT @DebugModeFlag = 'N' 
    ----------------------------------------------------------------------------------------
    -- Empty the [ImportAHSAHospitalDetails] table
    IF @DebugModeFlag = 'Y' PRINT 'Empty the [ImportAHSAHospitalDetails] table';
    ----------------------------------------------------------------------------------------    

    DELETE FROM [ImportAHSAHospitalDetails];

    DBCC CHECKIDENT ('ImportAHSAHospitalDetails', RESEED, 0);

    DELETE 
    FROM dbo.ImportProviderWarnings
    WHERE ImportType = 'Z'


    DECLARE @ProcessID    INT,
        @EffDateText  VARCHAR(10),
        @EffDate      DATETIME,
        @FileVersion  VARCHAR(10),      
        @InsertCount  INT,
        @UpdateCount  INT,
        @InvalidCount INT,
        @ProcessStartTime DATETIME,
        @ProcessEndTime DATETIME,
        @ProcessDuration INT

    DECLARE @HOSDetailsRaw TABLE (HOSDetailsData XML)
    DECLARE @HOSDetailsXML XML  

    SELECT  @ProcessStartTime=GETDATE() 

    ----------------------------------------------------------------------------------------
    -- Importing Raw Data File
    IF @DebugModeFlag = 'Y' PRINT 'Importing Raw Data File';
    ----------------------------------------------------------------------------------------
    INSERT INTO @HOSDetailsRaw EXEC ('SELECT * FROM OPENROWSET ( BULK ''' + @Path + @FileName + ''', SINGLE_CLOB) AS xmlData')
    SELECT @HOSDetailsXML = HOSDetailsData FROM @HOSDetailsRaw;

    ----------------------------------------------------------------------------------------
    -- Get the Effective Date
    IF @DebugModeFlag = 'Y' PRINT 'Get the Effective Date';
    ----------------------------------------------------------------------------------------
    WITH XMLNAMESPACES( 'http://www.ahsa.com.au/schema/hospitaldetails' AS ns)
    SELECT
        @EffDateText = @HOSDetailsxml.value('(/ns:HospitalDetails/@effectiveDate)[1]', 'VARCHAR(10)' );

    SELECT @EffDate = DATEFROMPARTS (SUBSTRING(@EffDateText, 1, 4), SUBSTRING(@EffDateText, 6, 2), SUBSTRING(@EffDateText, 9, 2));  

    ----------------------------------------------------------------------------------------
    -- Get the File Version
    IF @DebugModeFlag = 'Y' PRINT 'Get the File Version';
    ----------------------------------------------------------------------------------------
    WITH XMLNAMESPACES( 'http://www.ahsa.com.au/schema/hospitaldetails' AS ns)
    SELECT
        @FileVersion = @HOSDetailsxml.value('(/ns:HospitalDetails/@fileVersion)[1]', 'VARCHAR(10)' );