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
Best Answer
The agent itself is switching
QUOTED_IDENTIFIER
toOFF
.I have written about it here (mind the unreadable font, not my choice).
Here are the tests run on sql server 2012 to sql server 2017:
If I run this query in a ssms query window:
The result shows as on:
When I put the query to check the quoted identifier in a job and run it, this happens:
View the results:
Result:
When tracing the agent, you can clearly see the statement that turns quoted identifier off.
To resolve this you have to add explicit
SET QUOTED_IDENTIFIER ON;
statements to the agent job steps.