none
Could not complete cursor operation because the set options have changed since the cursor was declared RRS feed

  • Question

  • I get this error :Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958).  The step failed. by running my storage procedure if it's starded with a job.

     

    The same sp starded with Management Studio like exec my_sp runs without problems.

     

    Can you help me?

     

     

    Friday, January 18, 2008 8:28 AM

Answers

  • I get a workaround.

     

    As I runned the job with sqlcmd -E -Sservername -Q"exec sp_start_job @job_name = 'jobname' I get more details about the lines where to find the problem.

     

    The problem was when I call the exec (@stmtcur) to declare a "dynamic" cursor. The workaround is to put the cursor data into a temp table and then to declare the cursor.

     

    I'm to deploy my proc on many servers. I hope that is the solution.

    Code Snippet

    -- Old statement

                            -- -------------------------------------------
                            -- get schema names
                            -- -------------------------------------------
                            set @stmtcur = 'use ['+@dbname+']; DECLARE cur_schema INSENSITIVE CURSOR FOR SELECT distinct(schema_id) from ['+@dbname+'].sys.objects where schema_id <> USER_ID(''sys'')'
                            --PRINT @STMTCUR
                            exec (@stmtcur)

    -- New statement

    set @stmt = 'truncate table #tschemaname; use ['+@dbname+']; insert into #tschemaname select name from ['+@dbname+'].sys.schemas where schema_id != USER_ID(''sys'') and schema_ID < 16000 and name != ''guest'' and name != ''NT AUTHORITY\SYSTEM'' and name != ''INFORMATION_SCHEMA'''

    exec (@stmt)

    DECLARE cur_schema INSENSITIVE CURSOR FOR select schemaname from #tschemaname

     

     

     

    Friday, April 18, 2008 9:22 AM

All replies

  • Can you post the SP code?  Without seeing it, I can only recommend that you ensure that your database options align with that of your stored procedure.  I recall seeing this issue in the past with Arithabort.  The SP usually has SET ARITHABORT OFF, you can try adding SET ARITHABORT ON at the top of the create statement, and see if that helps solve the problem.

     

    so it would be something like :

     

    Code Block

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ARITHABORT OFF

    GO

    SET ARITHABORT ON

    GO

    CREATE PROCEDURE [dbo].[CustOrderHist]

     

     

     

     

    Friday, January 18, 2008 2:48 PM
    Moderator
  • Here the code my sp. As you can see, I added the set options. Without success.

     

    Code Block
    USE [IT2_SysAdmin]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ARITHABORT OFF
    GO
    SET ARITHABORT ON
    GO
    /* ************************************************************************************************
    ** PROCEDURE
    ** ***********************************************************************************************/
      -- ------------------------------------------------------------------------------------------------
      -- create procedure
      -- ------------------------------------------------------------------------------------------------
      ALTER  PROCEDURE [dbo].[USP_GET_CMDB_INFOS]
        AS
          -- --------------------------------------------------------------------------------------------
          -- variables declaration
          -- --------------------------------------------------------------------------------------------
          DECLARE @debugbit         bit           -- 1 if print for debug porpose are on, 0 if no debug
                , @tcpport          varchar(50)   -- output TCP/IP Port
                , @tcpipportdyn     varchar(50)   -- is TCP/IP Port dynamic
                , @tcpipportdynbit  bit           -- is TCP/IP Port dynamic bit
                , @servername       varchar(50)   -- server name
                , @pathname         varchar(20)   -- folder path name (MSSQL.?)
                , @instancename     varchar(50)   -- sql server instance name
                , @srvintname       varchar(50)   -- complete name of the instance (servername\instancename)
                , @sqledition       varchar(50)   -- SQL Server Edition
                , @sqlversion       varchar(50)   -- SQL Server Version
                , @sqlsrvicepack    varchar(50)   -- SQL Server service pack
                , @keyname          varchar(512)  -- key name
                , @dbname           varchar(50)   -- database name
                , @schemaname       varchar(50)   -- schema name
                , @schemaid         int           -- schema id
                , @dbstatus         sql_variant   -- database status (online or not)
                , @stmt             varchar(4000) -- statment
                , @stmtcur          varchar(4000) -- statment for cursors
                , @usr              varchar(100)  -- user (pext - post)
          -- --------------------------------------------------------------------------------------------
          -- loggings settings
          -- --------------------------------------------------------------------------------------------
    --      DECLARE @applname   AS  VARCHAR(50)   /* application name for usp_ins/del-event */
    --            , @logkeep    AS  INT           /* days to keekp log infos */
    --      -- Settings
    --      SET @applname = 'getCmdbInfos'
    --      SET @logkeep = 4
    --      -- drop old events in logs table from it2_sysadmin database
    --      exec usp_delevent @applname, @logkeep
          -- --------------------------------------------------------------------------------------------
          -- runnings settings
          -- --------------------------------------------------------------------------------------------
          SET NOCOUNT ON
          -- ----------------------------------------------------------------------------
          -- check if has to start
          -- ----------------------------------------------------------------------------
          SELECT * from IT2_SysAdmin..cmdb_start
          IF @@rowcount = 0 return
          -- ----------------------------------------------------------------------------
          -- Set Values
          -- ----------------------------------------------------------------------------
          SET @debugbit = 0
          SELECT @servername    = CONVERT(varchar(20), SERVERPROPERTY('machinename'))
          SELECT @instancename  = CONVERT(varchar(20), ISNULL(SERVERPROPERTY('instancename'), 'MSSQLServer'))
          SELECT @srvintname    = CONVERT(varchar(50), SERVERPROPERTY('servername'))
          SELECT @sqledition    = CONVERT(VARCHAR(50), SERVERPROPERTY('Edition'))
          SELECT @sqlversion    = CONVERT(VARCHAR(50), SERVERPROPERTY('ProductVersion'))
          SELECT @sqlsrvicepack = CONVERT(VARCHAR(50), SERVERPROPERTY('ProductLevel'))
          -- ----------------------------------------------------------------------------
          -- drop tables and create it new if not exists (rather than truncate it)
          -- ----------------------------------------------------------------------------
          /* server infos */
          -- ---------------
          -- SQL Server 2000
          -- ---------------
          IF (SUBSTRING(@sqlversion,1,1)=8)
          BEGIN
            IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_server]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_server]
            IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_server]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_server]
                  (  srvname     varchar(50)
                   , instname    varchar(50)
                   , srvintname  varchar(50)
                   , sqledition  varchar(50)
                   , sqlversion  varchar(50)
                   , sqlsp       varchar(50)
                   , dport       varchar(10)
                   , port        varchar(10)
                  )
            /* databases infos */
            IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_db]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_db]
            IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_db]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_db]
                  (  srvname   varchar(50)
                   , dbname    varchar(50)
                   , nameduser varchar(50)
                   , schemas   varchar(50)
                  )
            /* schemas infos */
            IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_schema]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_schema]
            IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[cmdb_schema]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_schema]
                  (  srvname    varchar(50)
                   , dbname     varchar(50)
                   , schemaname varchar(256)
                  )
          END
          -- ---------------
          -- SQL Server 2005
          -- ---------------
          IF (SUBSTRING(@sqlversion,1,1)=9)
          BEGIN
            IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_server]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_server]
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_server]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_server]
                  (  srvname     varchar(50)
                   , instname    varchar(50)
                   , srvintname  varchar(50)
                   , sqledition  varchar(50)
                   , sqlversion  varchar(50)
                   , sqlsp       varchar(50)
                   , dport       varchar(10)
                   , port        varchar(10)
                  )
            /* databases infos */
            IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_db]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_db]
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_db]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_db]
                  (  srvname   varchar(50)
                   , dbname    varchar(50)
                   , nameduser varchar(50)
                   , schemas   varchar(50)
                  )
            /* schemas infos */
            IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_schema]') AND type in (N'U'))
               DROP TABLE [dbo].[cmdb_schema]
            IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmdb_schema]') AND type in (N'U'))
               CREATE TABLE [dbo].[cmdb_schema]
                  (  srvname    varchar(50)
                   , dbname     varchar(50)
                   , schemaname varchar(256)
                  )
          END
          -- ---------------------------
          -- Version differentitation
          -- ---------------------------
          -- ---------------
          -- SQL Server 2000
          -- ---------------
          IF (SUBSTRING(@sqlversion,1,1)=8)
             BEGIN
                /* get @keyname, differant if default instance or named instance */
                IF @instancename = 'MSSQLServer'
                   set @keyname = 'SOFTWARE\Microsoft\'+@instancename+'\MSSQLServer\SuperSocketNetLib\Tcp'
                ELSE
                   set @keyname = 'SOFTWARE\Microsoft\Microsoft SQL Server\'+@instancename+'\MSSQLServer\SuperSocketNetLib\Tcp'
                /* get tcp/ip port is dynamic */
                EXEC master..xp_regread
                    @rootkey='HKEY_LOCAL_MACHINE'
                  , @key=@keyname
                  , @value_name='TcpDynamicPorts'
                  , @value=@tcpipportdyn OUTPUT
                IF @tcpipportdyn <> ''
                  BEGIN
                    SET @tcpipportdynbit = 1
                    SET @tcpport = 0
                  END
                ELSE
                  BEGIN
                    SET @tcpipportdynbit = 0
                    /* get tcp/ip port number */
                    EXEC master..xp_regread
                        @rootkey='HKEY_LOCAL_MACHINE'
                      , @key=@keyname
                      , @value_name='TcpPort'
                      , @value=@tcpport OUTPUT
                    END
             END -- end begin for sql 2000
          -- ---------------
          -- SQL Server 2005
          -- ---------------
          IF (SUBSTRING(@sqlversion,1,1)=9)
             BEGIN
                /* get instance name */
                SET @keyname   = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
                EXEC master..xp_regread
                    @rootkey='HKEY_LOCAL_MACHINE'
                  , @key=@keyname
                  , @value_name=@instancename
                  , @value=@pathname OUTPUT
                  --print @pathname
                /* get tcp/ip port is dynamic */
                set @keyname = 'SOFTWARE\Microsoft\Microsoft SQL Server\'+@pathname+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
                      -- print @keyname
                EXEC master..xp_regread
                    @rootkey='HKEY_LOCAL_MACHINE'
                  , @key=@keyname
                  , @value_name='TcpDynamicPorts'
                  , @value=@tcpipportdyn OUTPUT
                IF @tcpipportdyn <> ''
                  BEGIN
                    SET @tcpipportdynbit = 1
                    SET @tcpport = 0
                  END
                ELSE
                  BEGIN
                    SET @tcpipportdynbit = 0
                    /* get tcp/ip port number */
                    set @keyname = 'SOFTWARE\Microsoft\Microsoft SQL Server\'+@pathname+'\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
                        -- print @keyname
                    EXEC master..xp_regread
                        @rootkey='HKEY_LOCAL_MACHINE'
                      , @key=@keyname
                      , @value_name='TcpPort'
                      , @value=@tcpport OUTPUT
                    END
             END -- end begin for sql 2005
          -- ----------------------------------------------------------------------------
          -- insert server infos
          -- ----------------------------------------------------------------------------
          insert into cmdb_server (srvname, instname, srvintname, sqledition, sqlversion, sqlsp, dport, port) values (@servername, @instancename, @srvintname, @sqledition, @sqlversion, @sqlsrvicepack, @tcpipportdynbit, @tcpport)
          -- ----------------------------------------------------------------------------
          -- get databases and schemas infos
          -- ----------------------------------------------------------------------------
          /* cursor to get db names */
          DECLARE cur_db INSENSITIVE CURSOR FOR
             select name from master..[sysdatabases]
             where name not in ('master', 'msdb', 'model', 'tempdb', 'it2_sysadmin')
          OPEN cur_db
          FETCH NEXT FROM cur_db
          INTO @dbname
          WHILE @@FETCH_STATUS = 0
             BEGIN
                SELECT @dbstatus = DATABASEPROPERTYEX ( @dbname , 'Status')
                IF ( cast(@dbstatus as varchar) = 'ONLINE') /* control if the db is online */
                   BEGIN
                      IF (SUBSTRING(@sqlversion,1,1)=8) /* for sql server 2000 */
                         BEGIN
                            insert into cmdb_db (srvname, dbname) values (@srvintname, @dbname)
                            set @stmt = 'update IT2_SysAdmin..cmdb_db set nameduser = (select count(*) from ['+@dbname+']..sysusers where isntgroup = 1 or isntuser  = 1 or issqluser = 1) where dbname = '''+@dbname+''''
                           ">mailto:'''+@dbname+''''">'''+@dbname+''''
                           ; -- print @stmt
                            exec (@stmt)
                            set @stmt = 'update IT2_SysAdmin..cmdb_db set schemas = 1 where dbname = '''+@dbname+''''
                           ">mailto:'''+@dbname+''''">'''+@dbname+''''
                           ; -- print @stmt
                            exec (@stmt)
                            -- -------------------------------------------
                            -- get schema names
                            -- -------------------------------------------
                            /*
                               SQL 2000
                            */
                         END
                      IF (SUBSTRING(@sqlversion,1,1)=9) /* for sql server 2005 */
                         BEGIN
                            insert into cmdb_db (srvname, dbname) values (@srvintname, @dbname)
                            --print 'after insert'
                            set @stmt = 'update IT2_SysAdmin..cmdb_db set nameduser = (select count(*) from ['+@dbname+'].sys.database_principals where type in (''S'', ''G'', ''U'')) where dbname = '''+@dbname+''''
                           ">mailto:'''+@dbname+''''">'''+@dbname+''''
                           ; --print @stmt
                            exec (@stmt)
                            set @stmt = 'use ['+@dbname+']; update IT2_SysAdmin..cmdb_db set schemas = (select count(distinct(USER_NAME(schema_id))) from ['+@dbname+'].sys.objects where schema_id <> USER_ID(''sys'')) where dbname = '''+@dbname+''''
                           ">mailto:'''+@dbname+''''">'''+@dbname+''''
                           ; exec (@stmt)
                            -- -------------------------------------------
                            -- get schema names
                            -- -------------------------------------------
                            set @stmtcur = 'use ['+@dbname+']; DECLARE cur_schema INSENSITIVE CURSOR FOR SELECT distinct(schema_id) from ['+@dbname+'].sys.objects where schema_id <> USER_ID(''sys'')'
                            --PRINT @STMTCUR
                            exec (@stmtcur)
                            OPEN cur_schema
                            FETCH NEXT FROM cur_schema INTO @schemaid
                            WHILE @@FETCH_STATUS = 0
                               BEGIN
                                 SET @stmt = 'use ['+@dbname+'];declare @schemaname varchar(128); select @schemaname = USER_NAME('+cast(@schemaid as varchar)+');insert into IT2_SysAdmin..cmdb_schema (srvname, dbname, schemaname) values ('''+@srvintname+''','''+@dbname+'''mailto:'''+@srvintname+''','''+@dbname+'''">'''+@srvintname+''','''+@dbname+'''</A< A>>, @schemaname)'
                                 exec (@stmt)
                                 FETCH NEXT FROM cur_schema INTO @schemaid
                               END
                               CLOSE cur_schema
                               DEALLOCATE cur_schema
                         END
                    END
                FETCH NEXT FROM cur_db
                INTO @dbname
             END
          CLOSE cur_db
          DEALLOCATE cur_db
        -- ----------------------------------------------------------------
        -- stop job running
        -- ----------------------------------------------------------------
        DELETE from IT2_SysAdmin..cmdb_start
      -- ------------------------------------------------------------------------------------------------
      -- EOF
      -- ------------------------------------------------------------------------------------------------

     

     

     

    Monday, January 21, 2008 8:07 AM
  • I can't seem to reproduce the problem you report when I add this to my system.  It executes just fine as a job, as well as manually, and the results exist in the tables as I would expect based on my databases.  The only thing I can think of, is to check each of your databases options and or extended properties and see if there isn't some set option that is causing the cursor to change scope when you execute your @stmtcur variable.

    Monday, January 21, 2008 7:15 PM
    Moderator
  • I added some DBCC USEROPTIONS in my code to get the options setted. I can not get any differance at any time. I get the problem somewhere ~middle the last cursor. I'm still searching a solution.

     

    Monday, January 28, 2008 7:22 AM
  • I get a workaround.

     

    As I runned the job with sqlcmd -E -Sservername -Q"exec sp_start_job @job_name = 'jobname' I get more details about the lines where to find the problem.

     

    The problem was when I call the exec (@stmtcur) to declare a "dynamic" cursor. The workaround is to put the cursor data into a temp table and then to declare the cursor.

     

    I'm to deploy my proc on many servers. I hope that is the solution.

    Code Snippet

    -- Old statement

                            -- -------------------------------------------
                            -- get schema names
                            -- -------------------------------------------
                            set @stmtcur = 'use ['+@dbname+']; DECLARE cur_schema INSENSITIVE CURSOR FOR SELECT distinct(schema_id) from ['+@dbname+'].sys.objects where schema_id <> USER_ID(''sys'')'
                            --PRINT @STMTCUR
                            exec (@stmtcur)

    -- New statement

    set @stmt = 'truncate table #tschemaname; use ['+@dbname+']; insert into #tschemaname select name from ['+@dbname+'].sys.schemas where schema_id != USER_ID(''sys'') and schema_ID < 16000 and name != ''guest'' and name != ''NT AUTHORITY\SYSTEM'' and name != ''INFORMATION_SCHEMA'''

    exec (@stmt)

    DECLARE cur_schema INSENSITIVE CURSOR FOR select schemaname from #tschemaname

     

     

     

    Friday, April 18, 2008 9:22 AM
  • I faced the similar issues and i SET the database miscellaneous set options to false and ran the job, its ran successfully.
    Tuesday, March 19, 2013 12:02 PM
  • I was getting this error when running a script that was hitting two databases.

    The problem was that I had copied one of the databases from SQL 2012 into my SQL 2014 instance, and the other database was created in the SQL 2014 instance.

    I set the compatibility mode on the 2012 database to 2014, and that took care of the error.


    Best regards,

    Kendall Darveaux
    Partner Online Technical Community
    -----------------------------------------------------------------------------------------
    We hope you get value from our new forums platform! Tell us what you think:
    http://social.microsoft.com/Forums/en-US/partnerfdbk/threads
    ------------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights


    • Proposed as answer by glennfa Thursday, October 5, 2017 6:53 PM
    • Unproposed as answer by glennfa Thursday, October 5, 2017 6:53 PM
    Tuesday, February 16, 2016 5:49 PM
  • In my case, there was a "USE msdb" statement within the cursor.  I removed that and prefaced the msdb objects referenced with "msdb.dbo." and that resolved the problem.  I would make sure there are no "USE" statements in your cursor.
    Thursday, October 5, 2017 6:56 PM