Some SQL, some .NET, and whatever else


SQL Agent job fails with QUOTED_INDENTIFIER error

If you have a SQL Agent job that's failing, but the query succeeds when you run it manually in SQL Management Studio, it's possibly a difference between the SQL Server default connection settings and those SSMS uses as the default. In my case, I was inserting from a stored procedure and received the following error:

[SQLSTATE 42000] (Error 1934) INSERT 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.

To view the default connection options for your SQL Server instance, right-click on your instance in SSMS and select "Properties", and then browse to "Connections" - you'll see something like the following list (on my server, none of these are enabled by default for new connections):

Server Defaults

To compare it to the default settings for your installation of SSMS, click on "Tools" and then "Options...", and then browse to "Query Execution" -> "SQL Server" and view the settings under both "Advanced" and ANSI" (In my case, SSMS was enabling a number of settings by default for my connections that SQL Agent jobs wouldn't have enabled):


SSMS Results Advanced

In my case, I just added an explicit "SET QUOTED_IDENTIFIER ON" to the beginning of the script in my SQL Agent job and it resolved the error message. Hope this helps you too!


“Invalid server” error while altering SQL Agent Job step

When updating a SQL Agent job step to run an SSIS package instead of executing an SSAS script, I received the following error when trying to save the Agent Job:

The specified '@server' is invalid (valid values are returned by sp_helpserver).
The specified '@server' is invalid (valid values are returned by sp_helpserver)

I initially thought this error had something to do with my package connections, but it didn't - it occurred because I had changed the job step from an SSAS script to an SSIS package, rather than creating a new step and removing the old one. This actually seemed to occur when I alter the "SQL Server Analysis Services Command" step to any other type of step - it must leave some portion of the step partially configured, and it throws a validation error when you try to save it.


To work around this error, delete the old "SSAS Command" step, rather than modifying it, and created a new step to run your SSIS package. It will validate without error and let you save the updated package.