Transact SQL – Error – “INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ ….”

 

Background

Trying to go to a rescheduled Christmas/End of Year Company event, but getting this unfortunate error:


Msg 1934, Level 16, State 1, Procedure usp_pruneData, Line 10
INSERT 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  know it is weeks for a Christmas party, but we had a storm in December, and everything was shut down for a few days.

 

Analysis

 

List Objects / Columns with computed columns

 


SELECT
          object_schema_name(tblSC.object_id) as schemaName
        , object_name(tblSC.object_id) as objectName
        --, tblSO.object_id as objectID
        , OBJECTPROPERTYEX(tblSO.object_id, 'IsQuotedIdentOn') as isQuotedIdentifierOnDuringObjectCreation
        , tblSC.[name] as columnName
        --, tblSC.[column_id] as columnID
        , tblCC.[is_computed] as columnIsComputed
        , tblCC.[is_persisted] as columnIsPersisted
        , tblCC.[definition] as columnDefinition

FROM   sys.columns tblSC

            inner join sys.objects tblSO

                on tblSC.object_id = tblSO.object_id

            inner join sys.Computed_columns tblCC

                on tblSC.object_id = tblCC.object_id
                and tblSC.column_id = tblCC.column_id

WHERE  tblSC.is_computed = 1

 

listObjectsColumnsWithComputedColumns

List all Stored Procedures created with “Is Quoted Identifier” Off


/*
    List All Procedures created with "Is Quoted Identifier" Off
*/
SELECT
          object_schema_name(tblSO.object_id) as schemaName
        , tblSO.name as objectName

FROM  sys.objects tblSO

WHERE OBJECTPROPERTYEX(tblSO.object_id, 'IsQuotedIdentOn') = 0 

and   tblSO.type in ('P')

order by tblSO.modify_date desc

 

I cannot show our result grid as we have 266 such objects; objects created with “Quoted Identifier” off.

I also know that I can not possibly tackle all 266 objects at the same time.

Too much Change Control hurdles to surmount.

 

 

List only Stored Procedures that reference the tables with computed columns


/*
    List All Procedures created with Is Quoted Identifier Off
*/
;with cteObjectsWithComputedColumns
as
(

    SELECT 

          object_schema_name(tblSC.object_id) as schemaName
        , object_name(tblSC.object_id) as objectName
        , object_schema_name(tblSO.object_id) + '.' + tblSO.name as fullObjectName
        , tblSO.object_id as objectID
        , OBJECTPROPERTYEX(tblSO.object_id, 'IsQuotedIdentOn') as isQuotedIdentifierOnDuringObjectCreation
        , tblSC.[name] as columnName
        , tblSC.[column_id] as columnID
        , tblCC.[is_computed] as columnIsComputed
        , tblCC.[is_persisted] as columnIsPersisted
        , tblCC.[definition] as columnDefinition

    FROM   sys.columns tblSC

                inner join sys.objects tblSO

                    on tblSC.object_id = tblSO.object_id

                inner join sys.Computed_columns tblCC

                    on tblSC.object_id = tblCC.object_id
                    and tblSC.column_id = tblCC.column_id

    WHERE  tblSC.is_computed = 1

)

SELECT distinct

          object_schema_name(tblSO.object_id) as schemaName

        , tblSO.name as objectName

        , isNull(tblRE.referenced_database_name, db_name())
            as referencedDatabaseName

        , tblRE.referenced_schema_name
            as referencedSchemaName

        , tblRE.referenced_entity_name
            as referencedEntityName

FROM  sys.objects tblSO

        cross apply sys.dm_sql_referenced_entities(object_schema_name(tblSO.object_id) + '.' + tblSO.name, 'Object') tblRE

        inner join cteObjectsWithComputedColumns cteOWCC

            on cteOWCC.objectID = tblRE.referenced_id
            --and cteOWCC.columnName = tblRE.referenced_minor_name

/* Quoted Identifier was set on during object's creation */
WHERE OBJECTPROPERTYEX(tblSO.object_id, 'IsQuotedIdentOn') = 0 

/* Get Only Stored Procedures */
and   tblSO.type in ('P')

/* We have a schema name */
and   tblRE.referenced_schema_name is not null

order by
          object_schema_name(tblSO.object_id) 

        , tblSO.name

Here is our result:

pertinentObjects

 

 

Fix

Here is our fix.

 

 

Original


set noexec off
go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

if (db_name() = 'master')
begin

    print 'Can not create in master!'
    set noexec on

    return
end
go

Revised


set noexec off
go

SET ANSI_NULLS ON
GO

--SET QUOTED_IDENTIFIER OFF
SET QUOTED_IDENTIFIER ON
GO

if (db_name() = 'master')
begin

    print 'Can not create in master!'
    set noexec on

    return
end
go

Explanation:

  • And, so this one is an easy fix.
  • Our original code has “set quoted_identifier off“.  Commenting that out and replacing with “set quoted_identifier on” makes us good
  • In the case of Stored Procedures where “set quoted_identifier” can be set within the Object Creating Script, please be sure to review the code-line and make sure that it is not set.  But, as Shaun Tinline-Jones’ pointed out, when set otherwise outside the programmable object’s code, the session’s setting overrides and is effectual

 

Dedication

Technical

All the applause goes to Microsoft’s Shaun Tinline-Jones.

Author :- Shaun Tinline-Jones
Technical Reviewers: Thomas Kejser, Steve Howard, Jaime Alva Bravo, Kun Cheng, Jimmy May
“QUOTED_IDENTIFIER” causes Unexpected Query Plan for Persisted Computed Column query
http://blogs.msdn.com/b/sqlcat/archive/2011/08/31/unexpected-query-plan-for-computed-column.aspx

 

 

Personal

Personally, I will like to dedicate this post to the most well meaning colleagues. Quite a few of them have served the company for over 30 years ( Kenny – 36 years, Jay – 35 or so years [moving to Seattle], Moses a Corporate Architect/Strategist – Took the early retirement).

As Loon will say this thing is temporal and has an expiration date.

And, so from me to you, young mind, enjoy and write up little stories along the way. There is someone a bit younger hoping that you plant a solid feet and have lived it well enough to have good ones to share.

Brad Paisley “Letter To Me” | Live at the Grand Ole Opry | Opry
https://www.youtube.com/watch?v=5DOMY9rOPeg

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s