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?
SET QUOTED_IDENTIFIER OFF
has to be called explicitly for this behavior to occur as default behavior for this option isON
for most client drivers, user tools (e.g. SSMS), etc... basically most practical scenarios.