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?