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

Error

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] (Error 1934).

Problem

If you do index maintenance using DMO, then quoted_identifier will not always be preserved. 

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see “Considerations When You Use the SET Statements” in SET Statements (Transact-SQL).

Fix

-- Will Work
SET QUOTED_IDENTIFIER ON

-- Will Fail
SET QUOTED_IDENTIFIER OFF

Sources:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b6ff8aa0-2491-46ea-aa9c-4c6c912827e3/problem-insertupdatedelete-failed-because-the-following-set-options-have-incorrect-settings?forum=sqldatabaseengine

http://technet.microsoft.com/en-us/library/ms174393.aspx

SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage