UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job 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
Why am I getting this error in this scenario?


The agent itself is switching QUOTED_IDENTIFIER to OFF.

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:

DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';  
IF ( (256 &amp; @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';  
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;  


The result shows as on:

QUOTED_IDENTIFIER
ON


When I put the query to check the quoted identifier in a job and run it, this happens:

create table dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER varchar(3));


DECLARE @QUOTED_IDENTIFIER VARCHAR(3) = 'OFF';  
IF ( (256 &amp; @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON';  
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER;  

insert into dbo.QUOTEDIDENTIFIER_CHECK(QUOTEDIDENTIFIER)
select @QUOTED_IDENTIFIER;



View the results:

select * from dbo.QUOTEDIDENTIFIER_CHECK;


Result:

QUOTEDIDENTIFIER
OFF


When tracing the agent, you can clearly see the statement that turns quoted identifier off.




  How do I resolve this? Do I need to re-create all the Agent jobs,
  switching QUOTED_IDENTIFIER ON before I do?


To resolve this you have to add explicit SET QUOTED_IDENTIFIER ON; statements to the agent job steps.