如何在Server1编写SQL语句删除Server2数据表中的记录？

你的思路方向是对的，用OPENROWSET连接远程服务器执行操作是可行的路径，但需要完善动态SQL的构造，同时还要注意一些前置配置和安全细节。下面是完整的实现方案：

完整可运行代码示例 #

以下是调整后的代码，能在Server1中执行，完成Server2指定数据表的记录删除：

DECLARE @EmployeeType NVARCHAR(50) = 'FACT'
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ServerName NVARCHAR(MAX) = 'Server2'; -- 替换为实际的Server2名称
DECLARE @DatabaseName NVARCHAR(MAX) = 'DBName'; -- 替换为目标数据库名
DECLARE @TableName NVARCHAR(MAX) = 'YourTargetTable'; -- 替换为要删除记录的表名
DECLARE @UserID NVARCHAR(MAX) = 'UserId'; -- 远程服务器的登录账号
DECLARE @DBPassword NVARCHAR(MAX) = 'Password'; -- 远程服务器的登录密码

BEGIN
    -- 构造动态SQL，通过OPENROWSET连接远程服务器并执行DELETE
    SET @SQL = N'DELETE FROM OPENROWSET(
                    ''SQLNCLI'',
                    ''SERVER=' + @ServerName + ';DATABASE=' + @DatabaseName + ';UID=' + @UserID + ';PWD=' + @DBPassword + ''',
                    ''SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE EmployeeType = ''''' + @EmployeeType + '''''
                )'

    -- 执行拼接好的动态SQL
    EXEC sp_executesql @SQL
END

关键注意事项 #

• 启用Ad Hoc Distributed Queries：Server1上必须先开启这个配置，否则OPENROWSET无法正常使用，执行以下命令开启：

  sp_configure 'show advanced options', 1;
  RECONFIGURE;
  sp_configure 'Ad Hoc Distributed Queries', 1;
  RECONFIGURE;
  

• 权限要求：Server1的执行账号需要有运行OPENROWSET的权限，同时远程Server2的@UserID必须拥有目标表的DELETE权限。
• 防范SQL注入：直接拼接字符串存在注入风险，建议改用参数化方式优化，示例如下：

  SET @SQL = N'DELETE FROM OPENROWSET(
                  ''SQLNCLI'',
                  ''SERVER=' + @ServerName + ';DATABASE=' + @DatabaseName + ';UID=' + @UserID + ';PWD=' + @DBPassword + ''',
                  ''SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE EmployeeType = @EmpType''
              )'
  EXEC sp_executesql @SQL, N'@EmpType NVARCHAR(50)', @EmpType = @EmployeeType
  

• 更优替代方案：如果需要频繁跨服务器操作，建议创建链接服务器（Linked Server），比OPENROWSET更稳定易管理，示例：

  -- 先创建链接服务器
  EXEC sp_addlinkedserver 
      @server = 'Server2_Linked',
      @srvproduct='',
      @provider='SQLNCLI',
      @datasrc='Server2';
  
  EXEC sp_addlinkedsrvlogin 
      @rmtsrvname='Server2_Linked',
      @useself='FALSE',
      @rmtuser='UserId',
      @rmtpassword='Password';
  
  -- 直接执行删除操作
  DELETE FROM Server2_Linked.DBName.dbo.YourTargetTable WHERE EmployeeType = @EmployeeType
  

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