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

68de4m5k  于 2022-10-03  发布在  其他
关注(0)|答案(3)|浏览(248)

我们目前使用以下命令对链接服务器执行存储过程:

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

除了我们这样做的时候:一切都下了地狱:

消息7201,级别17,状态4,流程获取客户,第1行

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

比失败更糟糕? 

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

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

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

并重新运行我的过程调用:

EXECUTE Screwdriver.CRM.dbo.GetCustomer 619

消息7201,级别17,状态4,流程获取客户,第1行
无法在远程服务器‘ScrewDriver’上执行过程,因为没有为远程访问配置SQL Server。要求您的系统管理员重新配置SQL Server以允许远程访问。

比失败更糟糕!?

我可以使用以下命令确认服务器链接服务器(而不是*“远程”*服务器):

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

汇总

我们现在的情况是:

  • 我关闭了远程访问
  • 远程访问**仅适用于通过sp_addserver添加的服务器
  • 不适用于通过sp_addlinkedserver添加的服务器
  • 我正在访问通过sp_addlinkedserver添加的服务器

为什么它不起作用?

这就引出了我的问题:

  • 如何在链接的服务器上执行存储过程?

由此得出的结论是:

  • 如何针对*添加的(即“远程”)*服务器执行存储过程?

奖金闲聊

您使用sp_configure调整的远程访问配置选项也通过用户界面显示。SSMS用户界面错误地描述了该功能:

不正确的措辞是:

允许远程连接此服务器

应表述为:

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

Books Online还错误地记录了该功能:

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

控制从运行SQL Server示例的远程服务器执行存储过程。选中此复选框与将SP_CONFIGURE Remote Access选项设置为1具有相同的效果。清除此复选框将阻止从*远程服务器执行存储过程。

它应该是:

允许远程连接从此服务器

控制从运行SQL Server示例的远程服务器的存储过程的执行。选中此复选框与将SP_CONFIGURE Remote Access选项设置为1具有相同的效果。清除此复选框可防止从远程服务器执行存储过程。

这是有道理的,如今微软的年轻人已经不记得一个他们从未接触过的20年前被弃用的功能是什么了。

BOL 2000文档

SQL Server 2000是最后一次记录此功能。此处转载以供后代和调试之用:

配置远程服务器

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

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

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

另见

备用标题:禁用SQL Server远程访问会破坏存储过程。

fnatzsnv

fnatzsnv1#

好吧,当你是对的,你就是对的,无论它是否被记录在案。将remote access设置为0(并重新启动)会导致使用四部分语法的远程存储过程调用失败,即使所有文档都建议对链接服务器不应失败。即使在最新版本的SQL Server 2017(RTM CU12)上也是如此,因此这不是特定于版本的。目前还不清楚这是不是一个真正的限制,或者代码只是有错误,并基于remote access功能检查阻止它,尽管它在技术上可以工作。

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

作为一种解决办法,您可以将呼叫更改为使用EXECUTE .. AT

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

只要链接服务器启用了RPC Out选项(如果由sp_addlinkedserver在没有特殊选项的情况下添加,则默认情况下会启用RPC Out选项),就可以执行此操作。

遗憾的是,当涉及参数时,EXECUTE .. AT就不那么方便了,因为它只支持?语法:

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

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

如果您的过程有OUTPUT参数,这个简单的方法将不起作用;EXECUTE .. AT不够聪明,不能这样做。您可以在调用中指定OUTPUT,但不会复制回该值。解决这一问题的办法超出了本答案的范围,但它们不会很好。

ogsagwnx

ogsagwnx2#

我已经在2016和2019版的SQL Server上进行了测试,运行良好。您可以从配置中禁用远程访问,并且您将能够在链接服务器上执行该过程。您只需在链接服务器的参数中启用RPC OUT选项。

63lcw9qa

63lcw9qa3#

微软文档article已经从今天起进行了改写(完全公开:我改写了它)。该功能仍处于弃用状态,因此我们的建议是您应该将其保留为打开状态。

它现在说(增加了强调):
本文介绍的是远程访问配置选项,它是一种已弃用的SQL Server到SQL Server的通信功能。

此选项会影响使用sp_addserver和sp_addlinkedserver添加的服务器。如果使用链接服务器,则应启用远程访问(默认设置)

相关问题