I get this error message occasionally when I execute a stored procedure that pulls information from multiple linked servers and aggregates the data. It looks similar to what I have below.

DECLARE Cur CURSOR FOR SELECT name FROM sys.servers WHERE is_linked = 1 AND (@Environments IS NULL OR name IN (SELECT Item FROM clrStringSplit(@Environments, ',')))
OPEN cur
FETCH NEXT FROM cur INTO @Environment

    SET @Query = '          
        EXEC ' + @Environment + '.db.dashboard.storedproc ''' + CAST(@StartDate AS VARCHAR(10)) + ''', ''' + CAST(@EndDate AS VARCHAR(10)) + ''''

    INSERT INTO #Table (Environment, Column1, Column2, Column3, Column4, Column5, Column6, Column7)
    PRINT 'There was an error trying to query against ' + @Environment

FETCH NEXT FROM cur INTO @Environment

The stored procedure it calls on each server is the exact same on each one and is a basic select statement that pulls from tables underneath the dbo schema. It will "randomly" error out with the message of "Could not complete cursor operation because the table schema changed after the cursor was declared." Sometimes it runs fine, other times not. Each time its ran, even if back to back will sometimes work and sometimes not. What could possibly cause this to happen and why is it so intermittent? The error can be generated on any given call to a procedure on one of the servers. It's not always the same server it tries to query against that produces the error.

It is worth mentioning that I have several every procedures that do something extremely similar but never produce the issue. But they are all under the same schema (dbo) and this is the first time where a different schema is used.

EDIT - I forgot to mention that I had tried using a FAST_FORWARD cursor and it does not seem to help.

  • I had this error as well when calling multiple stored procedures from within a cursor. A few of the stored procedures had WITH RECOMPILE in their definition. These were unnecessary, and once I removed the recompile I no longer got the error. Apr 16, 2023 at 17:21

1 Answer 1


Try explicitly setting your cursor to be LOCAL STATIC FORWARD_ONLY.

I don't know what specific changes might be happening on your linked servers, or what that stored procedure might be touching, but static makes a copy of the data so that the cursor isn't impacted (well, doesn't care about) any of those changes.

This is the same issue with sp_MSforeachdb that causes it to skip databases. They use the wrong kind of cursor there and, thanks to error handling, databases that have been touched in any way since the cursor opened just get silently skipped. This is why I wrote a replacement that is also available - with some handy improvements - in Brent Ozar Unlimited's First Responder Kit.

As an aside, there is a better way to execute dynamic SQL on databases/servers supplied by a variable. This usually makes it possible to use sp_executesql and pass strongly typed parameters (instead of doubling up quotes all over the place, making messy conversions of ints and dates to strings, and so on).

DECLARE @exec nvarchar(512);

... while loop ...

SET @exec = @Environment + N'.db.sys.sp_executesql';

   Environment, Column1, Column2, Column3, 
     Column4, Column5, Column6, Column7
EXEC @exec N'EXEC dashboard.storedproc @StartDate, @EndDate;',
   N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.