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

Question

Tuesday, February 12, 2019 9:26 AM

Hello,

I have an application SQL job which failed(just one iteration) by throwing this below error

Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943)  Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] (Error 16943).  The step failed.

I noticed there is a maintenance job(rebuild index) which starts (04 AM)at the same time when the application job starts.  Could someone explain to me this
? Does this occur due to blocking?  Is there a permanent fix?

All replies (3)

Tuesday, February 12, 2019 9:37 AM

https://dba.stackexchange.com/questions/218760/could-not-complete-cursor-operation-because-the-table-schema-changed-after-the-c

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Tuesday, February 12, 2019 10:56 PM | 1 vote

Yes, the index rebuild count as a schema change.

Change the declaration of the index to include the word STATIC. See the example below.

CREATE TABLE soppig (accno int NOT NULL CONSTRAINT pk_soppig PRIMARY KEY(accno))

INSERT soppig SELECT object_id FROM sys.objects

DECLARE @cur CURSOR

SET @cur = CURSOR -- STATIC
   FOR SELECT TOP 10 accno FROM soppig

OPEN @cur

WHILE 1 = 1
BEGIN 
   FETCH  @cur
   IF @@fetch_status <> 0 
      BREAK

   ALTER INDEX pk_soppig ON soppig REBUILD
END
go
DROP TABLE soppig

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Wednesday, February 13, 2019 7:45 AM

So you need to combine the two jobs into one jobs, first job step is maintenance job, second job step is cursor operation. In this way, the error message would not occur again.

Best Regards,

Will

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

  • Last updated on