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?
Best Answer
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??