UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' in Agent job

Question

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.

1. Why am I getting this error in this scenario?
2. 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

Answer 1

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 & @@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 & @@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.

Answer 2

In my case I had to add to the step:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

And then the creation of the computed column.

ALTER TABLE ...

This way the Agent jobs run smoothly