UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job
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.