In brief, I added a computed column to a table and then Agent jobs began failing and reporting the 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).
An earlier question suggests that stored procedures must be created with QUOTED_IDENTIFIER set ON. In my case, however, there are no stored procedures. One example Agent job has one step and that contains straight SQL to carry out two updates - each update is against the table I altered. None of that SQL contains a double-quote character (which is the subject of QUOTED_IDENTIFIER).
Microsoft documentation says:
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views.
... however I simply added the column (persisted). I have not created or altered any indexes.
- Why am I getting this error in this scenario?
- How do I resolve this? Do I need to re-create all the Agent jobs, switching QUOTED_IDENTIFIER ON before I do? Or do I need to switch QUOTED_IDENTIFIER ON before altering the table to add the computed column?
Possibly related, the following SQL shows which objects in the database had QUOTED_IDENTIFIER ON at compile time (ie. which will be interpreted with that value when used). From this I see that triggers are also affected by this setting. Perhaps the Agent job update is invoking a trigger which is somehow related? I do note, however, that all objects are listed as uses_quoted_identifier = 1. In other words, if the resolution was to re-create objects while setting QUOTED_IDENTIFIER ON prior to re-creating them, I don't see what difference it would make; all objects already seem to have been created that way.
SELECT uses_ansi_nulls, uses_quoted_identifier, name, *
FROM sys.sql_modules -- WHERE object_id = object_id('SampleProcedure')
left outer join sys.sysobjects on sql_modules.object_id = sysobjects.id