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?