6

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?

1 Answer 1

12

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

NUTS

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.