INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

Question

I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache. As part of step 1 in the SQL job,i filter only the serial plans and insert it into a table i created. When i run the job,in step 1 itself i get the below error;

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

I did research and learned that it might be due to the SET options. So i checked the table i created and it is created with

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

I saw posts where people have the same issue ,and solved it after changing the above SET options to ON. I have this SET already ,but still same error.i am not sure why i am getting this error.

I have this line in the query where i get the serial plans.

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )

This is how it looks like :

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS (
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
--------------------
--------------------
----------------------
))
INSERT INTO table_name
select * from relop
WHERE relop.total_relop = relop.serial_relop

When i run it on the query window,there is no issues. When i put that in a job,i get this error.

Any suggestions?

Answer 1

It's not the way the table was created, it's the options your query runs with.

Agent does indeed run with the wrong settings.

SELECT 
    s.*
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
AND    
(   
       s.ansi_nulls = 0
    OR s.ansi_padding = 0
    OR s.ansi_warnings = 0
    OR s.arithabort = 0
    OR s.concat_null_yields_null = 0
    OR s.quoted_identifier = 0 
);

The simple fix would be to use the correct options at the beginning of the job step that does the insert.

For instance, if I create this table, an insert works just fine:

USE master

CREATE TABLE dbo.whatever (id INT, thing AS id * 2);

INSERT dbo.whatever ( id )
VALUES ( 0 )

But from an Agent job step it fails, unless I put this first:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;

For reference: Create Indexed Views