1

I have a SQL agent job that runs nightly at 2am successfully for the last year; We haven't made any change to that job specifically but it suddenly gives error:

MERGE 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 operation

I read around and looks like a quick fix is to insert the command "SET QUOTED_IDENTIFIER ON" at the very top of this job.

However, I really want to know why this happens? the job does not call any store procedure, instead it uses merge, insert, update using some views, tables.

I verified all my views and tables have quoted_identifier set to true which I believe is the default value.

I ran the following script and I see that my SQLAgent - Job Invocation Engine has quoted_identifier set to off.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    quoted_identifier = 0 ;

This perhaps explains why my job fails? But why it fails all of a sudden with the same job that ran successful the night before?

Could a script that adds new index or alter a store procedure caused this to quoted_identifier value to false? can a database restart do it?

Improve this question
2
  • This may help: stackoverflow.com/questions/1243991/…
    – Alex
    Dec 18, 2019 at 20:43
  • What code is the SQL Agent job executing? Is there a Stored Procedure being referenced at all? Could that SP have been updated recently? In most cases SET QUOTED_IDENTIFIER OFF has to be called explicitly for this behavior to occur as default behavior for this option is ON for most client drivers, user tools (e.g. SSMS), etc... basically most practical scenarios.
    – John Eisbrener
    Dec 19, 2019 at 16:27

1 Answer 1

Reset to default
1

Clearly something has caused this to be changed

SET QUOTED_IDENTIFIER must be ON when you invoke XML data type methods.

This can be set at the ODBC/OLEDB/ADO.NET layer, or at the SQLAGENT

MSSQL Client Config

Something changed the SQLAgent script or modified the Job parameters

If there is script running that alters the stored procedure and dropped the SET command. Yes that could do it.. Why would this stored procedure need to be altered??

Improve this answer
3
  • 2
    It could also be that a new filtered index or view index was created, since the job and views have not changed.
    – Dan Guzman
    Dec 19, 2019 at 3:22
  • I'm sorry I can not see how a creation of index could cause the required command Set Quoted Identifier to off to happen unless its running in the same session SQL Agent runs the XML merge? for it to work for a year then stop means the SQL Agent scripts have been changed
    – zsheep
    Dec 19, 2019 at 3:35
  • 2
    The job will ran fine with QUOTED_IDENTIFIER OFF as the question indicates. It is the introduction of a new filtered/view/XML index that caused the job to fail. The correct solution as @Fylix found is to add QUOTED_IDENTIFIER ON, although it could have fixed by dropping the problem index(es).
    – Dan Guzman
    Dec 19, 2019 at 11:08

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.