Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Could not complete cursor operation because the table schema changed after the cursor was declared

Posted on 2004-11-17
9
Medium Priority
?
15,480 Views
Last Modified: 2011-08-18
Hi,

I hope someone out there can shed some light on what is going on here.

We have a Java application which is running for 24 hours and we have implemented our own connection pooling. We are connected to SQL Server 2000 with SP3 installed.

The problem is that from time to time (usually only when a stored procedure has run wich creates and deletes temporary tables) suddenly one of the queries that is run many times (not always the same query) fails every time it is executed. The error is as follows:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not complete cursor operation because the table schema changed after the cursor was declared.

We have restarted our app (recreting connections) but sometimes it doesn't make any difference, and the only way to surely remove the error is to restart the db.

This error doesn't occur regularly, or in every environments. It seems to be an intermittent error i.e. some bug in SQL Server.

We managed to get around this problem by running the following system store procedures but that results in Memory Leak in our java application.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Any help would be much appreciated

Thanks Ed
0
Comment
Question by:eddeane
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Accepted Solution

by:
sukumar_diya earned 750 total points
ID: 12602805
Hi,
Are you changing the columns list in the table after cursor declaration. Is it possible to post the script here.
It will be helpful for the experts here to find a solution ...

Suku
0
 

Author Comment

by:eddeane
ID: 12603019
Hi Suku

The code's fairly long so I've removed the general processing from it (selects, inserts, updates, deletes), is this enough?

CREATE  PROCEDURE PROC1 @FileName VARCHAR(300) AS
BEGIN
   
--   Author : Ed Deane
 
--Drop the global temp table ##FINANCIAL if it already exists
   IF  EXISTS (select * from tempdb..sysobjects WHERE name = '##FINANCIAL')
   BEGIN
      DROP TABLE ##FINANCIAL
   END

--Drop the global temp table ##FINANCIAL if it already exists
   IF  EXISTS (select * from tempdb..sysobjects WHERE name = '##FILE')
   BEGIN
      DROP TABLE ##FILE
   END

   CREATE TABLE ##FINANCIAL (
            CUSTOMERS1 varchar(50)  NULL,
            LOANREFNO varchar(50)  NULL,
            AMOUNT numeric (12,2)  NULL,
            TRANSACTIONDATE DATETIME  NULL,
            CURRENCY varchar(50)  NULL,
            VALUEDATE smalldatetime NULL,
            CREDITREASON varchar(50)  NULL,
            DEBITREASON varchar(50)  NULL,
            STATUS VARCHAR(10) NULL,
            ACCOUNTACTIVITYID VARCHAR(10) NULL,
            ACCOUNTID VARCHAR(10) NULL,
            BALANCEID VARCHAR(10) NULL,
            ACTIVITYID VARCHAR(10) NULL,
            EVENTID VARCHAR(10) NULL,
            CREDITAMOUNT NUMERIC(12,2) NULL,
            DEBITAMOUNT NUMERIC(12,2) NULL,
            BALANCE NUMERIC(12,2),
            CUSTOMERID NUMERIC(10,0),
            NARRATIVE VARCHAR(50)
   )

      CREATE INDEX ixAcct On ##Financial (AccountID)
      CREATE INDEX ixAcctActivity On ##Financial (AccountActivityID)
      CREATE INDEX ixBalance On ##Financial (BalanceID)
      Create Index ixCust On ##Financial (CustomerID)

      CREATE TABLE ##FILE (
            RECORD VARCHAR(200)
   )

   DECLARE C_FILE CURSOR FOR
        SELECT RECORD
        FROM ##FILE

--GET HEADER INFO
   SET  @Commandline = 'BCP tempdb.##FILE IN "'+@IntRootDirectory+'\financial\'+@FileName+'" -S '+@@servername+' -U '+@dbusername+' -P password -c'
   EXEC @error=MASTER.DBO.XP_CMDSHELL @Commandline, NO_OUTPUT
   IF @Error <> 0
   BEGIN
      SET @ERRORTEXT = 'Error in importing file: '+@Commandline
      GOTO ERR_HANDLER  
   END      
   

/********************************************
--Selects, inserts, updates and deletes removed
********************************************/

      SET @v_errorcount = 0
   OPEN C_FILE

---  GET HEADER INFORMATION
   FETCH NEXT FROM C_FILE INTO @V_RECORD

   SET @V_FIRSTCOMMA    = CHARINDEX(',',@V_RECORD,1)
   SET @V_SECONDCOMMA   = CHARINDEX(',',@V_RECORD,@V_FIRSTCOMMA+1)
   SET @V_THIRDCOMMA    = CHARINDEX(',',@V_RECORD,@V_SECONDCOMMA+1)
   SET @V_HEADERNOOFRECORDS  = CAST((SUBSTRING(@V_RECORD,@V_FIRSTCOMMA+1,@V_SECONDCOMMA-@V_FIRSTCOMMA-1)) AS NUMERIC)
   SET @V_HEADERTOTALCREDITS = CAST((SUBSTRING(@V_RECORD,@V_SECONDCOMMA+1,@V_THIRDCOMMA-@V_SECONDCOMMA-1)) AS FLOAT)
   SET @V_HEADERTOTALDEBITS  = CAST((SUBSTRING(@V_RECORD,@V_THIRDCOMMA+1,LEN(@V_RECORD))) AS FLOAT)

--- GET FIRST RECORD FROM CURSOR    
   FETCH NEXT FROM C_FILE INTO @V_RECORD
   WHILE (@@FETCH_STATUS <> -1)
   BEGIN
      BEGIN TRANSACTION
     
      INSERT INTO ##FINANCIAL(--for each record)

/********************************************
--Selects, inserts, updates and deletes removed
********************************************/

      COMMIT TRANSACTION
     
      FETCH NEXT FROM C_FILE INTO @V_RECORD
   END

   IF @Error <> 0
   BEGIN
      SET @ERRORTEXT = 'Error sorting the financial file'
      GOTO ERR_HANDLER  
   END

   CLOSE C_FILE
   DEALLOCATE C_FILE


-- CREATE CURSOR FOR LOOP A (SELECT ALL NEW RECORDS APART FROM HEADER RECORD)

DECLARE C_FINANCIAL CURSOR FOR
      SELECT ACCOUNTID,AMOUNT,TRANSACTIONDATE,
      VALUEDATE,CURRENCY,CREDITREASON,
      DEBITREASON,STATUS,      ACCOUNTACTIVITYID
      FROM ##FINANCIAL
      ORDER BY ACCOUNTACTIVITYID

   OPEN C_FINANCIAL

   FETCH NEXT FROM C_FINANCIAL INTO @V_ACCOUNTID,@V_AMOUNT,@V_TRANSACTIONDATE,@V_VALUEDATE,@V_CURRENCY,
                                    @V_CREDITREASON,@V_DEBITREASON,@V_STATUS,@V_ACCOUNTACTIVITYID

   WHILE (@@FETCH_STATUS <> -1)
--Loop through and process all transactions individually

/********************************************
--Selects, inserts, updates and deletes removed
********************************************/
      FETCH NEXT FROM C_FINANCIAL INTO @V_ACCOUNTID,@V_AMOUNT,@V_TRANSACTIONDATE,@V_VALUEDATE,@V_CURRENCY,
                                      @V_CREDITREASON,@V_DEBITREASON,@V_STATUS,@V_ACCOUNTACTIVITYID
      END
      CLOSE C_FINANCIAL
      DEALLOCATE C_FINANCIAL

      ALTER TABLE ACCOUNTACTIVITIES DISABLE TRIGGER ACCOUNTACTIVITY_UPDATE
      ALTER TABLE BALANCES DISABLE TRIGGER BALANCE_UPDATE

/********************************************
--Selects, inserts, updates and deletes removed
********************************************/

       DROP TABLE ##FINANCIAL
      DROP TABLE ##FILE
      ALTER TABLE ACCOUNTACTIVITIES ENABLE TRIGGER ACCOUNTACTIVITY_UPDATE
      ALTER TABLE BALANCES ENABLE TRIGGER BALANCE_UPDATE

    SET @TEXT = CAST(GETDATE() AS VARCHAR(20))+': Procedure PROC1 completed successfully'
      EXEC DBO.LOGTEXT @TEXT
   Return(0)

ERR_HANDLER:
   IF (@ERRORTEXT != '')
/********************************************
--Selects, inserts, updates and deletes removed
********************************************/
      ALTER TABLE ACCOUNTACTIVITIES ENABLE TRIGGER ACCOUNTACTIVITY_UPDATE
      ALTER TABLE BALANCES ENABLE TRIGGER      BALANCE_UPDATE
      END -- Error Handler

END

Ed
0
 
LVL 7

Expert Comment

by:sukumar_diya
ID: 12603163
Hi,
Just looking at the script one thing i suspect..
You are dropping the global temp table .....

So if suppose one session is using that table and another one session ( another user or application instance) drops the table the problem may occur...

Instead of using Global Temp tables try to use local temp tables...

sukumar
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Hilaire
ID: 12603352
Agree with sukumar.
The problem is most likely that you use global temp tables.

Unfortunately using local temp tables won't help, since the BCP command creates its own scope of execution : BCP won't "see" the newly created local #temp table.

Alternatively you could use the BULK INSERT command instead of the BCP command-line utility to load
the data in a local #temp table ....

BULK INSERT is faster than bcp, and you can use it in QA and in a stored procedure whithout creating a new session/connection. Thus you won't "lose"  a local temporary table.

HTH
Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12603356
To sum up
- local temporary tables won't work with BCP
- they should work with BULK INSERT
0
 

Author Comment

by:eddeane
ID: 12605889
Thanks for all your fast feedback, it does seem likely that global temp tables are related to the problems. There are several queries I have though:

1. Why are the connections (from java and not using these temp tables, or even the tempdb) throwing the errors?

2. Why are the errors not being thrown from within the stored procedure?

3. The error being thrown in this way seems to be a bug, in which case is there not a fix for it, or at least is it not registered with Microsoft. (or am i missing the point)

Ed
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 750 total points
ID: 12606046
1) not familiar with java connections...
yet with OLEDB, connections give accesss to metadata, so data structure inconsistancy could result in connection throwing an err ? All errors are trapped at the connection level.

2) some errors in SQL Server 2000 are untrappable. It means in some cases the stored procedures just quit/resume and you never get an error code, and the error handler code is not executed.

3) related to 2) I think ...
Untrappable errors makes it difficult to code sometimes, but most of the times you can get rid of errors by making your code more robust. Next version of SQL Server will have better support for error handling, exceptions, ...
0
 

Author Comment

by:eddeane
ID: 12876614
Before you cleanup there, I'd better give an update.

First of all, sorry for not replying earlier but I din't feel we had any definite answer so I went off and did some testing of the possibilities.

Secondly, the memory leak was a red herring, it appears that the garbage collector was failing to keep up. When we dropped the number of threads we were working with to 4 from 10 (despite it benig an 8 cpu, 8Gb RAM server) then the leak disappeared. In an ideal world we would be able to look further into this but it's Christmas soon and there ain't time.

Thirdly, the error "Could not complete cursor operation because the table schema changed after the cursor was declared" comes about not because of using temp tables but because of the disabling of triggers mid procedure. This is now being managed better until a moe complete solution is found, it usually goes away after a few minutes but to force it out you can run the following to clear the caches:


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Thanks for your help, half points for everyone
Ed
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question