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

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
eddeaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sukumar_diyaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eddeaneAuthor Commented:
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
sukumar_diyaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HilaireCommented:
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
HilaireCommented:
To sum up
- local temporary tables won't work with BCP
- they should work with BULK INSERT
0
eddeaneAuthor Commented:
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
HilaireCommented:
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
eddeaneAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.