2

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.

  • 1
    Try explicitly LOCAL STATIC FORWARD_ONLY – Aaron Bertrand Sep 27 '18 at 19:33
  • That looks to have solved the issue. But why does it work? What is happening to cause this situation? – Eric S Sep 27 '18 at 20:35
5

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';

INSERT INTO #Table 
(
   Environment, Column1, Column2, Column3, 
     Column4, Column5, Column6, Column7
)
EXEC @exec N'EXEC dashboard.storedproc @StartDate, @EndDate;',
   N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate;
  • Thank you so much! I really appreciate the explanation. I'll look to updating things to that style as it would be so much easier to maintain. – Eric S Sep 28 '18 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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