SQL Server跨链接服务器执行DROP表报错前缀超过最大2个如何解决

问题根因 #

SQL Server的DROP TABLE语法不支持四部分命名格式（服务器名.数据库名.架构名.表名），最多仅支持2级前缀（数据库名.架构名的格式），而SELECT查询语法支持四部分的链接服务器对象引用，所以会出现查询正常、删表报错的情况。

解决方案 #

方案1：使用EXEC AT直接在远程源服务器执行删表操作（最常用） #

该方案要求你配置的链接服务器DBSourceServer开启了RPC OUT权限，执行语句如下：

Use [TargetDWHDB]    
-- 远程执行删表操作
EXEC ('DROP TABLE [SourceDWHTemp].[dbo].[SourceTable1]') AT [DBSourceServer];

如果未开启RPC OUT，可以先执行以下语句开启配置：

EXEC sp_serveroption 
    @server = N'DBSourceServer', 
    @optname = N'rpc out', 
    @optvalue = N'true';

方案2：在源服务器封装删表操作的存储过程调用 #

如果不希望开放远程执行权限，你可以提前在DBSourceServer的SourceDWHTemp库中创建专属的中间表清理存储过程，直接在目标服务器跨服务器调用该存储过程即可：

-- 源服务器SourceDWHTemp库中提前创建存储过程示例
-- CREATE PROC dbo.sp_DropTempTable @TableName NVARCHAR(128)
-- AS
-- EXEC ('DROP TABLE [dbo].' + QUOTENAME(@TableName))

-- 目标服务器侧调用语句
EXEC [DBSourceServer].[SourceDWHTemp].[dbo].[sp_DropTempTable] N'SourceTable1'

权限校验 #

执行操作前需要确认当前登录账号拥有DBSourceServer上SourceDWHTemp库的ALTER或者DELETE TABLE权限。

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