SQL Server 2005更新Azure链接服务器表时出现未知错误

问题分析 #

你遇到的错误虽提到游标，但实际是SQL Server处理跨链接服务器的UPDATE时，隐式使用了分布式游标，而Azure SQL DB不支持这种游标类型的强制计划。再加上JOIN中的COLLATE子句干扰了查询优化器的计划选择，最终触发了该错误。

解决方案 #

方案1：将UPDATE逻辑迁移到Azure侧执行（最优） #

先把本地临时表的数据同步到Azure的临时表，再在Azure本地执行UPDATE，避免跨服务器的分布式游标操作：

-- 1. 将本地临时表数据导入Azure临时表
INSERT INTO Azure.database.dbo.#remote_staging (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, col4, col5 FROM #staging;

-- 2. 在Azure侧执行UPDATE（通过链接服务器调用）
EXEC Azure.database.dbo.sp_executesql N'
    UPDATE T1
    SET T1.Col5 = T2.Col5
    FROM dbo.tablename T1
    INNER JOIN #remote_staging T2
        ON T1.[col1] = T2.[col1] COLLATE Latin1_General_CI_AS
        AND T1.[col2] = T2.[col2] COLLATE Latin1_General_CI_AS
        AND T1.[col3] = T2.[col3] COLLATE Latin1_General_CI_AS
        AND T1.[col4] = T2.[col4]
    WHERE T1.[col5] <> T2.[col5];
';

-- 3. 清理Azure临时表
EXEC Azure.database.dbo.sp_executesql N'DROP TABLE #remote_staging;';

方案2：给UPDATE语句添加查询提示 #

强制SQL Server使用兼容的游标类型，在原UPDATE语句末尾添加OPTION (FAST_FORWARD, RECOMPILE)提示：

UPDATE T1
SET T1.Col5 = T2.Col5
FROM
Azure.database.dbo.tablename T1 INNER JOIN #staging T2
ON  T1.[col1] = T2.[col1] COLLATE Latin1_General_CI_AS
AND T1.[col2] = T2.[col2] COLLATE Latin1_General_CI_AS 
AND T1.[col3] = T2.[col3] COLLATE Latin1_General_CI_AS
AND T1.[col4] = T2.[col4]
WHERE T1.[col5] <> T2.[col5]
OPTION (FAST_FORWARD, RECOMPILE); -- 添加这行提示

方案3：统一两边的COLLATE设置 #

如果Azure侧表的col1-col3列COLLATE和本地一致（比如都是Latin1_General_CI_AS），直接去掉JOIN中的COLLATE子句，让查询优化器生成更高效的计划：

UPDATE T1
SET T1.Col5 = T2.Col5
FROM
Azure.database.dbo.tablename T1 INNER JOIN #staging T2
ON  T1.[col1] = T2.[col1]
AND T1.[col2] = T2.[col2]
AND T1.[col3] = T2.[col3]
AND T1.[col4] = T2.[col4]
WHERE T1.[col5] <> T2.[col5];

方案4：使用OPENQUERY封装UPDATE逻辑 #

通过OPENQUERY将UPDATE操作推送到Azure侧执行，绕开本地的分布式游标：

EXEC Azure.database.dbo.sp_executesql N'
    UPDATE T1
    SET T1.Col5 = T2.Col5
    FROM dbo.tablename T1
    INNER JOIN OPENQUERY(LOCAL_SQL2005, ''SELECT col1, col2, col3, col4, col5 FROM #staging'') T2
        ON T1.[col1] = T2.[col1] COLLATE Latin1_General_CI_AS
        AND T1.[col2] = T2.[col2] COLLATE Latin1_General_CI_AS
        AND T1.[col3] = T2.[col3] COLLATE Latin1_General_CI_AS
        AND T1.[col4] = T2.[col4]
    WHERE T1.[col5] <> T2.[col5];
';

注意：需先将本地SQL Server 2005配置为Azure的链接服务器（命名为LOCAL_SQL2005）。

内容的提问来源于stack exchange，提问作者tkeen