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

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
3
down vote

favorite












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?










share|improve this question



























    up vote
    3
    down vote

    favorite












    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?










    share|improve this question























      up vote
      3
      down vote

      favorite









      up vote
      3
      down vote

      favorite











      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?










      share|improve this 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?







      sql-server sql sql-server-agent jobs






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Aug 28 at 20:51









      user9516827

      1489




      1489




















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          6
          down vote



          accepted










          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 *
          FROM sys.dm_exec_sessions
          WHERE is_user_process = 1
          AND ( ansi_nulls = 0
          OR ansi_padding = 0
          OR ansi_warnings = 0
          OR arithabort = 0
          OR concat_null_yields_null = 0
          OR 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






          share|improve this answer






















            Your Answer







            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            convertImagesToLinks: false,
            noModals: false,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













             

            draft saved


            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216116%2finsert-failed-in-sql-job-because-of-incorrect-set-options-quoted-identifier%23new-answer', 'question_page');

            );

            Post as a guest






























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            6
            down vote



            accepted










            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 *
            FROM sys.dm_exec_sessions
            WHERE is_user_process = 1
            AND ( ansi_nulls = 0
            OR ansi_padding = 0
            OR ansi_warnings = 0
            OR arithabort = 0
            OR concat_null_yields_null = 0
            OR 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






            share|improve this answer


























              up vote
              6
              down vote



              accepted










              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 *
              FROM sys.dm_exec_sessions
              WHERE is_user_process = 1
              AND ( ansi_nulls = 0
              OR ansi_padding = 0
              OR ansi_warnings = 0
              OR arithabort = 0
              OR concat_null_yields_null = 0
              OR 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






              share|improve this answer
























                up vote
                6
                down vote



                accepted







                up vote
                6
                down vote



                accepted






                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 *
                FROM sys.dm_exec_sessions
                WHERE is_user_process = 1
                AND ( ansi_nulls = 0
                OR ansi_padding = 0
                OR ansi_warnings = 0
                OR arithabort = 0
                OR concat_null_yields_null = 0
                OR 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






                share|improve this answer














                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 *
                FROM sys.dm_exec_sessions
                WHERE is_user_process = 1
                AND ( ansi_nulls = 0
                OR ansi_padding = 0
                OR ansi_warnings = 0
                OR arithabort = 0
                OR concat_null_yields_null = 0
                OR 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







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Aug 28 at 21:39

























                answered Aug 28 at 21:28









                sp_BlitzErik

                19.8k1162101




                19.8k1162101



























                     

                    draft saved


                    draft discarded















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216116%2finsert-failed-in-sql-job-because-of-incorrect-set-options-quoted-identifier%23new-answer', 'question_page');

                    );

                    Post as a guest













































































                    Popular posts from this blog

                    How to check contact read email or not when send email to Individual?

                    Displaying single band from multi-band raster using QGIS

                    How many registers does an x86_64 CPU actually have?