主页 / dba / 问题 / 218760
Accepted
Eric S
Asked: 2018-09-28 10:48:30 +0800 CST

无法完成游标操作，因为在声明游标后表架构发生了变化

  • 772

当我执行从多个链接服务器提取信息并聚合数据的存储过程时，偶尔会收到此错误消息。它看起来类似于我下面的内容。

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

它在每台服务器上调用的存储过程在每台服务器上都完全相同，并且是从 dbo 模式下的表中提取的基本选择语句。它将“随机”出错，并显示“无法完成游标操作，因为声明游标后表模式已更改”的消息。有时它运行良好，有时则不然。每次运行时，即使背靠背有时会起作用，有时不会。什么可能导致这种情况发生，为什么它如此断断续续？对其中一台服务器上的过程的任何给定调用都可能会生成错误。它尝试查询的服务器并不总是会产生错误。

值得一提的是，我有几个程序执行极其相似但从未产生问题的每个程序。但它们都在同一个模式（dbo）下，这是第一次使用不同的模式。

编辑 - 我忘了提到我曾尝试使用 FAST_FORWARD 游标，但它似乎没有帮助。

schema sql-server-2014

1 个回答

  1. Best Answer

    尝试将光标显式设置为LOCAL STATIC FORWARD_ONLY.

    我不知道您的链接服务器上可能会发生哪些特定更改，或者该存储过程可能会触及什么，但静态会复制数据以便光标不受影响（嗯，不关心）任何这些变化。

    这与sp_MSforeachdb导致它跳过数据库的问题相同。他们在那里使用了错误类型的游标，并且由于错误处理，自游标打开以来以任何方式触及的数据库都会被静默地跳过。这就是为什么我在 Brent Ozar Unlimited 的First Responder Kit中编写了一个也可用的替代品- 并进行了一些方便的改进。

    顺便说一句，有一种更好的方法可以在由变量提供的数据库/服务器上执行动态 SQL。这通常使得使用sp_executesql和传递强类型参数成为可能（而不是在所有地方加倍引号，将整数和日期转换为字符串，等等）。

    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;
    • 6

相关问题