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 WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY 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) EXEC(@Query) END TRY BEGIN CATCH PRINT 'There was an error trying to query against ' + @Environment PRINT ERROR_MESSAGE() END CATCH FETCH NEXT FROM cur INTO @Environment END
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.