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?