Sql-server – Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS

sql serversql server 2014sql-server-agentssms

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?

Best Answer

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.

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Have you checked which SQL user is actually running the query? Depending on the permission level, SQL Agent may be allowing the user to 'impersonate' a higher level account.

Create a job with the T-SQL step:

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME();

Make it log output to a table or text file and review which user is actually running the final query. If it's not the user you think it should be, then you'll need to trace back to work out where the impersonation is coming from.

I have ran into problems on CRM Dynamics databases using filtered views, where specified CRM users can see their filtered data. If the job is using a system account impersonation it'll return no data as the system account doesn't exist as a CRM user.

Sql-server – Cannot run Sql Server Agent jobs with PowerShell script

It sounds like you have a good way to reproduce the problem:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

The next step I'd take is to do some tracing to determine where the error is happening. Since you aren't getting a stack trace in your repro, I'd start by running Process Monitor.

I'd want to start by filtering on processes named Powershell.exe, and then adjust the filters based on the data captured. My suspicion is that this will turn out to be a problem with the PowerShell profile for the Windows service account, but hopefully with Process Monitor you'll get an idea of what error is leading up to the generic -1073741819, which is defined as being a default or unknown error in various Windows libraries.

Significant edit: According to the Visual Studio ERRLOOK feature, the error message of -1073741819 shown in the lower-right corner of the stack trace in your post translates to 0xC0000005. This is defined as STATUS_ACCESS_VIOLATION in ntstatus.h. It could be that some component is not correctly installed, or something is interfering with it (e.g. antivirus). If an access violation is occurring, there might also be further details in Windows' application log.

I'd suggest testing running Process Monitor in a non-production environment first until you feel familiar with it, and selecting the 'Drop Filtered Events' option to avoid consuming large amounts of memory from buffering all events.

Best Answer

Related Solutions

Sql-server – SQL Server Agent not observing “execute as” permissions

Sql-server – Cannot run Sql Server Agent jobs with PowerShell script

Related Question