none
Could not complete cursor operation because the table schema changed RRS feed

  • Question

  • Microsoft SQL Server  2000 - 8.00.2039

    Got this error:

    Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000

    Is this a known issue?  I suspect the application logic may cause this error. Please advise.

    Thanks a lot!

     

    Tuesday, May 30, 2006 8:50 PM

Answers

All replies

  • Is the message accurate?

    Does your app change the table(s) that the cursor is using?

    /Kenneth

    Wednesday, May 31, 2006 1:49 PM
    Moderator
  • I am running into the same issue.  The table is not being altered.  Here is an outline of what I am doing.

    A cursor loop is running in a stored procedure (looping through server and drive letters).  In the loop it calls a stored procedure that does the following:

    DBCC CHECKIDENT (zstblACLSStage,RESEED,0)

    SET @strSQL = 'INSERT INTO zstblACLSStage(LN) SELECT LN = RTRIM(LTRIM(REPLACE(F1,CHAR(9),'''')))
      FROM OPENROWSET
      (
      ''Microsoft.Jet.OLEDB.4.0''
      ,''Text;Database=' + @strDirectory + ';HDR=NO''
      ,''SELECT * FROM ' + @strFile + '''
      )
      WHERE ISNULL(REPLACE(F1,CHAR(9),''''),'''') != '''''
    PRINT @strSQL
    EXECUTE (@strSQL)

    --- More processing here transfers some data from zstblACLSStage to tblACLS

    DELETE FROM zttblACLSStage

    This is all run from a scheduled task in SQL.   If I kick the job off manually, it will run and complete.   If it runs scheduled for overnight, the EXECUTE will get the error message.   It happens the 7th time through the loop and it always happens.  I am suspecting the DBCC so I am commenting that out to give it another try, any suggestions are welcome.

     

    Tuesday, March 6, 2007 3:28 PM
  • Can you give the select statement that creates your cursor?

     

    Tuesday, March 13, 2007 10:44 AM
  • Hi All,

    Even I am getting the same error while querying the SQL 2000 database table from ASP. I use a select statement , containing outer joins, and sometimes this error pops up.

    Thanks,
    Tuesday, April 17, 2007 7:52 PM
  • Could you also update me if you get an answer on this. I am also facing similar issue. My system is throwing the same error every once in a while and if we do the same operation again, it works fine. I verified with maintenance jobs also and they are not conflicting.

     

    Please Help !!!!!

    Thursday, May 3, 2007 3:49 PM
  • Hi found this post on SQLServerCentral... may be your issue?


    "Auto-shrink can cause this error, if the auto-shrink kicks in and re-arranges data pages while the cursor is open. Same with maintenance plans that do re-indexing.

    Check neither of these are scheduled while your cursor process is running."


    Cheers

    John


    Wednesday, June 4, 2008 10:28 PM
  • the problem is about database buffer. the cursor, which has been created before any schema change that affects cursor and stored in database buffer, does not match with the new schema. cleaning the buffer will solve the problem, also server restart will create the same effect.

    DBCC FREEPROCCACHE

    the above command cleans the desired buffer.
    Friday, August 14, 2009 7:00 AM
  • the problem is about database buffer. the cursor, which has been created before any schema change that affects cursor and stored in database buffer, does not match with the new schema. cleaning the buffer will solve the problem, also server restart will create the same effect.

    DBCC FREEPROCCACHE

    the above command cleans the desired buffer.
    Now, I have the same error.what is surprise,this is no cursor defined in the used procedure. It is sql server 2005 here. when inserting some data from other instance's database into local database,it will occasionally come out. and I see your advice using 'dbcc freeproccache', but this may decline the query effiency. SO?
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    Tuesday, September 15, 2009 5:50 AM
  • I m also facing the same problem. In my situation, I am not calling any Stored Procs or Cursors but through the MFC classes. Help me out..
    • Proposed as answer by Paulino PP Tuesday, January 25, 2011 5:35 PM
    Tuesday, August 31, 2010 12:41 PM
  • I had the same issue. I was testing an SSIS package. It was failing running on SQL 2005 (SP2) or SQL 2008 (SP1). Neither one has AutoSrink on.
    My problem was that I had to concurrent tasks. One was adding rows to the table and the other task was creating an index on the same table. As soon as I changed it so that index was created after inserting all the rows, the problem went away. I hope it helps some one.
    Paulino
    Tuesday, January 25, 2011 5:39 PM
  • Reindexing the table while using a cursor can cause the same issue.

    Thursday, April 7, 2011 5:15 AM
  • Just change syntax to while declaring a cursor, it should work

    Declare cur1 insensitive cursor.

    thanks

    Wednesday, July 4, 2012 8:15 AM
  • I had this error too.

    I added "FAST_FORWARD FORWARD_ONLY" command after "DECLARE cur CURSOR" and my problem resolved.

    Good Luck


    Each problem has a solution.


    Sunday, September 30, 2012 8:29 AM
  • In some cases this was a bug:

    http://support.microsoft.com/kb/930775

    On the topic, cursor WHILE loops should be avoided whenever feasible. They do not scale well. Use set-based operations instead.

    A second advice, don't put any logic into a client application what can be done on the server side with stored procedures, functions, views, constraints and triggers.

    In one sentence: think sets, think server-side!


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Sunday, September 30, 2012 9:54 PM
    Moderator