Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS

Question

I have two T-SQL scripts that are scheduled to run once every day. Both do cleanup of some old data from different tables. Both scripts are run as the same user, on the same database.

Script A will execute successfully both in SQL Server Management Studio and when launched as a job, but Script B will only execute successfully in SQL Server Management Studio, even when logged in as the same user as is set up to run the script under SQL Server Agent.

Script B fails with the following error:

Executed as user [username].
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.

[SQLSTATE 42000] (Error 1934). The step failed.

I do have a filtered index on one of the tables I'm trying to clean, but why does it then work through SSMS?

Answer 1

The default for QUOTED_IDENTIFIERin SSMS is ON while it is OFF for SQLCMD, OSQL, BCP and in your case especially SQL Server Agent.

For reference, these are the different default options per application

+-------------------------+------------+------+-------------------+------------+
|                         |  ADO .Net, | SSMS | SQLCMD,OSQL, BCP  | ISQL       |
|                         |  ODBC      |      | SQL Server Agent  | DB-Library |
|                         |  OLE DB    |      |                   |            |
+-------------------------+------------+------+-------------------+------------+
| ANSI_NULL_DFLT_ON       | ON         | ON   | ON                | OFF        |
| ANSI_NULLS              | ON         | ON   | ON                | OFF        |
| ANSI_PADDING            | ON         | ON   | ON                | OFF        |
| ANSI_WARNINGS           | ON         | ON   | ON                | OFF        |
| CONCAT_NULL_YIELDS_NULL | ON         | ON   | ON                | OFF        |
| QUOTED_IDENTIFIER       | ON         | ON   | OFF               | OFF        |
| ARITHABORT              | OFF        | ON   | OFF               | OFF        |
+-------------------------+------------+------+-------------------+------------+

The above table is copied from Erland Sommarskog

So you would need to add SET QUOTED_IDENTIFIER ON as the first line in your job step.