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

Question

I am having a problem with an update stored procedure. The error is:

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

SQL State: 42000
Native Error: 1934

Unfortunately, there are no indexed views, computed columns, or query notifications for this table. This Stored Procedure was running fine for past couple of days and since today has been returning this error.

Is there any suggestion that would help in identifying the problem?

Note: If I set the quoted_identifier to ON and rerun the CREATE PROCEDURE, the issue will be fixed (for now). But I want to understand what triggered this issue in the first place.

Answer 1

To avoid that error, I needed to add

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

for all my stored procs editing a table with a computed column.

You don't need to add the SET inside the proc, just use it during creation, like this:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE dbo.proc_myproc
...

Answer 2

I got this error when I tried to run an sql file via the command line with sqlcmd:

sqlcmd -i myfile.sql

By default QUOTED_IDENTIFIER is set to OFF when using this command line tool and you will get the same error (no matter that in the SSMS it may be set to ON and the same script will pass).

So indeed the solution is to add this QUOTED_IDENTIFIER ON to your sql file like Jim suggested, or explicitly specify the flag -I:

sqlcmd -i myfile.sql -I

Answer 3

We cannot create a indexed view by setting the quoted identifier off. I just tried it and SQL 2005 throws an error straight away if it is turned off:

Cannot create index. Object 'SmartListVW' was created with the following SET options off: 'QUOTED_IDENTIFIER'.

As gbn said, rebuilding the indexes must be the only other way it got turned off.

I have seen lots of articles saying it must be on before creating index on views. Otherwise you would get an error while inserting, updating the table, but here I can get the error straight away, so sql engine won't allow to create index on views by setting it to off, per this msdn link.

I have asked a similar question here in stack sometime ago...

EDIT

I turned off the global queryexecution (in editor) ANSI settings and ran the index script in new editor, this time also it throws the same error. So it's clear we can't create indexes on views by turning off quoted_identifier.

Answer 4

I'm late to this party but had this error and wanted to share it.

Our problem was recurrent but random so we knew it wasn't an object that had been created incorrectly.

We finally tracked it down to an ODBC connection on one of the servers in our Citrix farm. On that server, the ODBC in question had had its QUOTED_IDENTIFIERS turned off (unchecked). On all the other servers, it was checked as expected. We turned the option on and the problem was instantly solved.

Answer 5

I got this error when I run SQL Agent Job, which has 3 steps T-sql scripts.

Msg 1934, Sev 16, State 1, Line 15 : UPDATE 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. [SQLSTATE 42000]

I added

SET ANSI_NULLS, QUOTED_IDENTIFIER ON; to the top of the Agent Job and that solved the issue.

Answer 6

Some thoughts:

Did indexes get rebuilt? If you do index maintenance using DMO, then quoted_identifier will not always be preserved. It can be a pain to track down and was a particular problem is SQL Server 2000 until SP4 or so.

However, I've seen on SQL Server 2005 some time ago too.

Answer 7

SELECT
    OBJECT_NAME (sm.object_id) AS [Name],
    sm.uses_ansi_nulls,
    sm.uses_quoted_identifier,
    N'SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
--change the below CREATE to an ALTER.
GO
' + sm.definition AS PossibleFixingStatement
FROM
    sys.sql_modules AS sm
WHERE
    1 = 1
AND
(
    sm.uses_ansi_nulls <> 1
OR  sm.uses_quoted_identifier <> 1
)
AND NOT EXISTS
(
    SELECT
        *
    FROM
        sys.objects AS o
    WHERE
        o.is_ms_shipped = 1
    AND o.object_id = sm.[object_id]
)
ORDER BY
    sm.uses_ansi_nulls,
    sm.uses_quoted_identifier;

Query to identify the affected objects. Part of the sp_blitz procedure mentioned here at https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1698

Answer 8

I got this error today running a stored procedure in SSMS. Disconnecting from the server and reconnecting with a new session solved the problem for me. The SP I was running had never had this problem before.

Answer 9

I got the same error running this query in the Job Scheduler SQL Server Agent

UPDATE [Order]
SET OrderStatusID = 100
WHERE OrderStatusID = 200
AND OrderID IN (    
        [...]
)

I solved removing the [ ] characters from [Order]:

UPDATE Order
SET OrderStatusID = 100
WHERE OrderStatusID = 200
AND OrderID IN (    
        [...]
)

No more errors

Answer 10

I got the same error, had to add a couple of settings to get it resolved:

SET ANSI_NULLS ON;  
SET ANSI_PADDING ON;  
SET ANSI_WARNINGS ON;  
SET ARITHABORT ON;  
SET CONCAT_NULL_YIELDS_NULL ON;  
SET NUMERIC_ROUNDABORT OFF;  
SET QUOTED_IDENTIFIER OFF;  
SET NOCOUNT ON;