DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

You may come across a scenario where your SQL Agent job scheduled to run a Delete statement against a table fails with the below error.


DELETE 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.

But the same delete statement runs fine when you run it from SSMS, so what’s wrong on my agent job??? Below is the reason for failure…
From the error message it is quite clear that delete operation has failed on a table which has 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.
So for these type of operations we need to Set Quoted_Identifier to ON, whenever a new connection is made through SSMS, by default, QUOTED_IDENTIFIER will be on but this is not the case for SQL Server Agent Job Step.

So for the Steps that are running Delete statements as a Job step should have the below code appended to it…

SET QUOTED_IDENTIFIER ON
Go
DELETE STATEMENT

Above change will make your job step to succeed…

Additional Info:

• You can use DBCC USEROPTIONS which returns the SET options active for the current connection.
• You can also enable Quoted_Identifier to ON at Server Level by clicking on Server properties Connections TabCheck “Quoted Identifier” Click Ok.