2

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
2

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;

enter image description here 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.

enter image description here

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.

2
  • Thanks Randi. I haven't managed to test this solution yet but when I do, if it succeeds, I'll be sure to come back here to accept your answer. – youcantryreachingme Feb 20 '20 at 5:03
  • @youcantryreachingme great! Thanks for getting back to me & Good luck :) – Randi Vertongen Feb 20 '20 at 7:05
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.