代码之家 › 专栏 › 技术社区 › Ian Boyd

如何对链接服务器执行存储过程?

sql-server-2008-r2 sql-server

Ian Boyd · 技术社区 · 6 年前

当前,我们对链接服务器执行存储过程时使用:

EXECUTE [LinkedServer].[DatabaseName].[dbo].[MyProcedure]

例如:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

这样做很好;通过链接服务器进行查询也很好。

禁用不推荐使用的“远程访问”功能

显然有一个鲜为人知、很少使用的特征,即 远程访问 . 微软对这项功能几乎无话可说是除了 say here:

此配置选项是不推荐使用的模糊的SQL Server到SQL Server通信功能,您可能不应该使用它。

重要的

此功能将在下一版本的Microsoft SQL Server中删除。不要在新的开发工作中使用此功能,并尽快修改当前使用此功能的应用程序。使用 sp_addlinkedserver 相反。

这个 远程访问 选项仅适用于通过使用 sp_addserver ,并包含以实现向后兼容性。

从SQL Server 2000联机丛书中:

注释对远程服务器的支持仅用于向后兼容。必须对SQL Server的远程实例执行存储过程的新应用程序应改用链接服务器。

我们只增加了 链接的 服务器,从未使用过 “远程访问” 功能,并且从未使用 sp_addserver .

我们都很好。对吗?

除了关闭远程访问会破坏一切

一位审计员提到我们应该关闭 远程访问 特点:

这是他们剪贴板上的安全复选框
它被微软否决了
几乎没用过
我们不使用它

应该没事吧?

Microsoft文档 how to turn off this hardly used feature:

配置远程访问服务器配置选项
EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO

除非我们这样做:一切都会陷入地狱:

MSG 7201,17级,状态4,程序getcustomer,第1行

无法在远程服务器上执行过程 'Screwdriver' 因为没有为远程访问配置SQL Server。要求系统管理员重新配置SQL Server以允许远程访问。

比失败更糟糕?

为了确保我使用的是链接服务器,我:

EXECUTE sp_dropserver @server='screwdriver', @dropLogins='droplogins'

EXECUTE sp_addlinkedserver N'screwdriver', N'SQL Server'

重新运行我的过程调用:

执行螺丝刀.crm.dbo.getcustomer 619

MSG 7201,17级,状态4,程序getcustomer,第1行
无法在远程服务器“螺丝刀”上执行过程,因为没有为远程访问配置SQL Server。要求系统管理员重新配置SQL Server以允许远程访问。

比失败更糟糕!?

我可以 confirm 那个服务器是链接服务器(而不是 “远程” 服务器):

SELECT SrvName, IsRemote 
FROM master..sysservers
WHERE SrvName = 'Screwdriver'

Srvname      IsRemote
-----------  --------
screwdriver  0

或使用现代物体:

SELECT Name, Is_Linked
FROM sys.servers
WHERE Name = 'Screwdriver'

Name         Is_linked
-----------  --------
screwdriver  1

总结

我们现在的处境是:

我已经残疾了 远程访问
远程访问 仅适用于通过添加的服务器 SPAdAdvServer
它不适用于通过 sp_addlinkedserver
我正在访问通过添加的服务器 添加链接服务器

为什么不起作用?

这让我想到了我的问题:

如何对 链接的 服务器?

其推论是:

如何不对 添加(即“远程”)。 服务器?

奖金喋喋不休

这个 远程访问 配置选项,您可以使用 sp_configure ,也通过用户界面公开。SSMS用户界面对功能的描述不正确:

有句话: 允许远程连接到这个服务器
应该是: 允许远程连接到从这个服务器。

这个 Books Online 同时错误地记录功能:

允许远程连接到此服务器

从运行SQL Server实例的远程服务器控制存储过程的执行。选择此复选框与设置 配置远程访问 选项为1。清除它会阻止从远程服务器执行存储过程。

应该是:

允许远程连接 到 从此服务器

控制存储过程的执行从到运行SQL Server实例的远程服务器。选择此复选框与设置 配置远程访问 选项为1。清除它会阻止存储过程的执行从到远程服务器。

现在微软的年轻人不记得他们从未接触过的一个20岁的被弃用的功能是什么,这是有道理的。

BOL 2000文件

SQL Server 2000是最后一次记录此功能。为子孙后代和调试目的在此复制:

配置远程服务器

远程服务器配置允许连接到一个Microsoft®SQL Server实例的客户端在另一个SQL Server实例上执行存储过程,而不建立另一个连接。客户机连接到的服务器接受客户机请求,并代表客户机将请求发送到远程服务器。远程服务器处理请求,并将任何结果返回给原始服务器,而原始服务器又将这些结果传递给客户机。

如果要设置服务器配置以便在另一台服务器上执行存储过程,但没有现有的远程服务器配置,请使用链接服务器而不是远程服务器。对于链接服务器,存储过程和分布式查询都是允许的;但是,对于远程服务器,只允许存储过程。

注释对远程服务器的支持仅用于向后兼容。必须对SQL Server的远程实例执行存储过程的新应用程序应改用链接服务器。

也见

替代标题:禁用SQL Server远程访问会中断存储过程。

1 回复 | 直到 6 年前

Jeroen Mostert 6 年前

好吧,当你是对的时候,你是对的,不管它是否被记录在案。设置 remote access 到 0 (和重新启动)会导致使用四部分语法的远程存储过程调用失败,即使所有文档都建议链接服务器不应失败。即使在最新版本的SQL Server 2017(RTM CU12)上也是如此,因此这不是特定于版本的。不清楚这是不是 真实的 限制,或者如果代码只是有缺陷的,并基于 远程访问 功能检查,即使技术上可行。

涉及四部分名称的查询( SELECT * FROM server.db.scheme.table )不会失败,大概是因为这只对链接服务器有效,而且一开始从不涉及远程访问。

作为解决方法,您可以将呼叫更改为使用 EXECUTE .. AT :

EXEC ('EXECUTE CRM.dbo.GetCustomer 619') AT Screwdriver

只要链接服务器具有 RPC Out 选项已启用(如果添加 sp_addlinkedserver 没有特殊选择)。

不幸地 执行…AT 当涉及参数时就不那么方便了,因为它只支持 ? 语法:

EXEC ('EXECUTE CRM.dbo.GetCustomer @Customer=?', 619) AT Screwdriver

这里的参数名是可选的,但我强烈建议使用它来保持可预测性。同样 EXECUTE --它是可选的,但它清楚地表明我们实际上正在运行一个任意查询,而不仅仅是调用存储过程。

如果你的程序 OUTPUT 参数,此平原不工作; 执行…AT 不够聪明。您可以指定 产量 在调用中,但该值不会被复制回。解决这个问题的方法超出了这个答案的范围,但它们不会很漂亮。

可能值得一试 suggestion 为此,因为它看起来像是一种真正应该按文档工作的东西(如果微软曾经想要永久摆脱 远程访问 不管怎样,他们都需要。