none
SQL : UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' error. RRS feed

  • Question

  • Hello Everyone : I have a SQL agent job set up and it executes the below query daily at certain time. Below is the query, which is supposed to execute through the job. 

     Its an Update statement : "update PhoneCallReceived set IsProcessed = 1 where IsProcessed = 0"

    Below is the Error : 
    Executed as user: NT SERVICE\SQLSERVERAGENT. 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). The step failed.

    How to fix this ? 

    Thanks

    Thursday, January 16, 2020 1:47 PM

Answers

  • Did you try this complex statement: ‘SET QUOTED_IDENTIFIER ON ; update PhoneCallReceived set IsProcessed = 1 where IsProcessed = 0’?

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Thursday, January 16, 2020 5:05 PM
  • By default, SQL Server runs with QUOTED_IDENTIFIER OFF. And please don't ask me to justify this nonsense.

    Supposedly, your table has a filtered index, an indexed computed column or similar.

    If you make it a habit to only run stored procedures from your Agent jobs, you will not run into this, since in SSMS QUOTED_IDENTIFIER is ON by default, and the setting is saved with the SP, so Agent's stupid default will not matter.

    Viorel_'s solution may work, but if it does not, wrap the the update in dynamic SQL:

    SET QUOTED_IDENTIFIER ON; EXEC('update PhoneCallReceived set IsProcessed = 1 where IsProcessed = 0')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Thursday, January 16, 2020 10:48 PM
  • Hi kkran,

    I will also advice you use 'SET QUOTED_IDENTIFIER ON'. For more information , please check SET QUOTED_IDENTIFIER (Transact-SQL).

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Friday, January 17, 2020 2:46 AM

All replies

  • Did you try this complex statement: ‘SET QUOTED_IDENTIFIER ON ; update PhoneCallReceived set IsProcessed = 1 where IsProcessed = 0’?

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Thursday, January 16, 2020 5:05 PM
  • By default, SQL Server runs with QUOTED_IDENTIFIER OFF. And please don't ask me to justify this nonsense.

    Supposedly, your table has a filtered index, an indexed computed column or similar.

    If you make it a habit to only run stored procedures from your Agent jobs, you will not run into this, since in SSMS QUOTED_IDENTIFIER is ON by default, and the setting is saved with the SP, so Agent's stupid default will not matter.

    Viorel_'s solution may work, but if it does not, wrap the the update in dynamic SQL:

    SET QUOTED_IDENTIFIER ON; EXEC('update PhoneCallReceived set IsProcessed = 1 where IsProcessed = 0')


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Thursday, January 16, 2020 10:48 PM
  • Hi kkran,

    I will also advice you use 'SET QUOTED_IDENTIFIER ON'. For more information , please check SET QUOTED_IDENTIFIER (Transact-SQL).

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by kkran Wednesday, January 22, 2020 4:58 PM
    Friday, January 17, 2020 2:46 AM