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

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

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

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

To sum up
- local temporary tables won't work with BCP
- they should work with BULK INSERT

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