使用动态SQL向链接服务器插入数据：存储过程执行无数据插入问题

1. 检查执行权限差异 #

• 直接在SSMS执行时用的是当前登录账号的权限，而存储过程默认采用调用者权限执行。如果调用存储过程的账号（比如SQL代理账号、其他应用账号）没有链接服务器的访问权限，就会出现执行成功但无数据插入的情况。
• 解决方法：
  • 在创建存储过程时指定WITH EXECUTE AS OWNER，让存储过程以创建者的权限执行：

    CREATE PROCEDURE YourInsertProcedure
    WITH EXECUTE AS OWNER
    AS
    BEGIN
        DECLARE @SqlCode NVARCHAR(MAX);
        SET @SqlCode = N'INSERT INTO [linked server].[Linked Server db].schema.tablename (column1,column2) SELECT column1,column2 FROM [source db].schema.tablename';
        EXEC sp_executesql @SqlCode;
    END
    

  • 或者直接给调用存储过程的账号分配链接服务器的访问权限。

2. 修正动态SQL的名称解析问题 #

• 硬编码的链接服务器/数据库名称可能因存储过程的数据库上下文不同导致解析错误，尤其是名称包含空格或特殊字符时。
• 解决方法：
  • 使用变量传递链接服务器和数据库名，通过拼接生成动态SQL，同时用sp_executesql替代EXEC（更安全且支持参数化）：

    CREATE PROCEDURE YourInsertProcedure
    AS
    BEGIN
        DECLARE @LinkedServer NVARCHAR(128) = N'linked server';
        DECLARE @LinkedDB NVARCHAR(128) = N'Linked Server db';
        DECLARE @SqlCode NVARCHAR(MAX);
    
        SET @SqlCode = N'INSERT INTO [' + @LinkedServer + N'].[' + @LinkedDB + N'].schema.tablename (column1,column2) 
                         SELECT column1,column2 FROM [source db].schema.tablename';
        EXEC sp_executesql @SqlCode;
    END
    

3. 排查事务处理逻辑 #

• 存储过程可能包含未提交的事务，或者错误被捕获后触发了隐性回滚，导致数据没有真正写入链接服务器。
• 解决方法：
  • 在存储过程中显式添加事务控制，确保异常时回滚、正常时提交：

    CREATE PROCEDURE YourInsertProcedure
    AS
    BEGIN
        SET NOCOUNT ON;
        BEGIN TRY
            BEGIN TRANSACTION;
            
            DECLARE @SqlCode NVARCHAR(MAX);
            SET @SqlCode = N'INSERT INTO [linked server].[Linked Server db].schema.tablename (column1,column2) SELECT column1,column2 FROM [source db].schema.tablename';
            EXEC sp_executesql @SqlCode;
            
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
            THROW; -- 抛出错误便于排查
        END CATCH
    END
    

4. 检查链接服务器的RPC设置 #

• 链接服务器需要启用RPC和RPC Out选项，否则远程执行动态SQL可能无法正常写入数据。
• 解决方法：
  • 先查询当前设置：

    SELECT name, is_rpc_out_enabled, is_rpc_enabled 
    FROM sys.servers 
    WHERE name = N'linked server';
    

  • 如果未启用，执行以下命令修改：

    EXEC sp_serveroption N'linked server', 'rpc out', 1;
    EXEC sp_serveroption N'linked server', 'rpc', 1;
    

5. 确认数据库上下文一致性 #

• 直接执行时的当前数据库与存储过程所在数据库不同，可能导致源表或链接服务器的引用异常。
• 解决方法：
  • 在动态SQL中显式指定所有对象的完整四部分名称，或者在存储过程开头切换到目标数据库：

    CREATE PROCEDURE YourInsertProcedure
    AS
    BEGIN
        USE [source db]; -- 切换到源数据库上下文
        DECLARE @SqlCode NVARCHAR(MAX);
        SET @SqlCode = N'INSERT INTO [linked server].[Linked Server db].schema.tablename (column1,column2) SELECT column1,column2 FROM schema.tablename';
        EXEC sp_executesql @SqlCode;
    END
    

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