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

I have written about how a reindex job can interrupt cursor operations:

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

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I got the chance to have another go at this issue. In previous posts, the cursor was declared asĀ is without specifying any attributes.

After going through all the attributes, I have decided to test a few and see if they would make any difference. I will spare you the trouble and list the findings below:

FAST_FORWARD

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

FORWARD_ONLY

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

READONLY

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

STATIC

It worked!

It was quite obvious why it worked in hindsight, according to the trusted Books Online:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

There you go, problem solved.