SQL Server:如何在链接服务器上调用用户定义函数(UDF)? [英] SQL Server: How to call a user-defined function (UDF) on linked server?

问题描述

我正在尝试在链接服务器上调用用户定义函数(UDF):

i'm trying to call a User-Defined Function (UDF) on a linked server:

CREATE FUNCTION [dbo].[UserGroupMembershipNames](@UserGUID uniqueidentifier)  
RETURNS VARCHAR(8000)
AS
BEGIN
    RETURN ASILIVE.ReportManager.dbo.UserGroupMembershipNames(@UserGUID)
END

这不起作用，如 PRB:四部分链接服务器中的用户定义函数调用中所述查询失败，并显示错误消息170 .他们还提供了一种解决方法:

This doesn't work, as documented in PRB: User-Defined Function Call in Four-Part Linked Server Query Fails with Error Message 170. They also give a workaround:

例如，代替以下查询

For example, instead of the following query

Select * from Linked_Server.northwind.dbo.square_value(10)

使用Openquery函数运行查询:

run a query with the Openquery function:

Select * from Openquery(Linked_Server,'select northwind.dbo.square_ value(10)')

如果用户定义的函数采用变量或标量参数，则可以使用sp_executesql存储过程来避免此行为.例如:

If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. For example:

exec Linked_Server.northwind.dbo.sp_executesql N'SELECT northwind.dbo.square_value(@input)',N'@input int',@input=10

我将如何将此变通方法应用于我的情况以及这家伙的情况?

How would i apply this workaround to my situation, and the situation of this guy?

换句话说:

如何在链接服务器上调用UDF?

推荐答案

要调用远程过程，您需要在链接服务器上激活RPC OUT.在SSMS中打开链接服务器的属性，然后单击服务器选项"，并确保RPC Out为True.

To call remote procedures, you need to activate RPC OUT on your Linked Server. Open the Linked Server's Properties in SSMS then click "Server Option" and make sure RPC Out is True.

然后... 您的链接可以解决您的问题.查看WorkAround的最后一个选项

And... Your link has the solution to your problem. Look at the last option in the WorkAround

"exec Linked_Server.northwind.dbo.sp_executesql N'SELECT northwind.dbo.square_value(@input)'，N'@ input int'，@ input = 10"

"exec Linked_Server.northwind.dbo.sp_executesql N'SELECT northwind.dbo.square_value(@input)',N'@input int',@input=10"

这是一个适合您的测试用例:

Here's a test case for you:

use master
go
EXEC master.dbo.sp_addlinkedserver @server = N'(LOCAL)', @srvproduct=N'SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'(LOCAL)',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;
EXEC master.dbo.sp_serveroption @server=N'(LOCAL)', @optname=N'rpc out', @optvalue=N'true'
GO
Use Testing
GO
CREATE FUNCTION [dbo].[UserGroupMembershipNames](@UserGUID uniqueidentifier)  
RETURNS VARCHAR(8000)
AS
BEGIN
    RETURN 'hello'
END
GO
select dbo.[UserGroupMembershipNames]('4278E0BF-2F7A-4D60-A09C-95E517E21EBC')
GO
exec [(LOCAL)].Testing.dbo.sp_executesql 
N'select dbo.UserGroupMembershipNames(@UserGUID)',N'@UserGUID uniqueidentifier'
,@UserGUID='4278E0BF-2F7A-4D60-A09C-95E517E21EBC'

这篇关于SQL Server:如何在链接服务器上调用用户定义函数(UDF)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！