SQL Server：如何在链接服务器上调用用户定义的函数（UDF）？

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

我试图在链接服务器上调用用户定义函数（UDF）：

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

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:

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

For example, instead of the following query

例如，而不是以下查询
Select * from Linked_Server.northwind.dbo.square_value(10)
run a query with the Openquery function:

使用Openquery函数运行查询：
Select * from Openquery(Linked_Server,'select northwind.dbo.square_ value(10)')
If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. For example:

如果用户定义的函数采用变量或标量参数，则可以使用sp_executesql存储过程来避免此行为。例如：
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?

我如何将这种解决方法应用于我的情况，以及这个人的情况？

In other words:

换一种说法：

How to call a UDF on a linked server?

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

4 个解决方案

#1

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.

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

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

并且......您的链接可以解决您的问题。查看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'

#2

Check This Link, which is my blog:

检查此链接，这是我的博客：

http://developersmania.blogspot.com/2012/11/call-user-defined-function-on-linked.html

Simple details of the above link is the function below.

以上功能的简单细节如下。

CREATE FUNCTION [dbo].Function_Name(@Parameter INT)
RETURNS VARCHAR(8000)
AS
BEGIN

    DECLARE @word sysname

    EXEC LinkedServer.DatabaseName.dbo.sp_executesql
        N'SELECT DatabaseName.dbo.Function_Name(@Parameter)' --dynamic sql query to execute
        ,N'@Parameter int' --parameter definitions
        ,@Parameter=@word OUTPUT --assigning the caller procs local variable to the dynamic parameter

    RETURN @word

END

#3

Try the following changes:

CREATE FUNCTION [dbo].[UserGroupMembershipNames](@UserGUID uniqueidentifier)  
RETURNS VARCHAR(8000)
AS
BEGIN
    declare @sql nvarchar(800)
    declare @param nvarchar(20)
    declare @innersql nvarchar(400)
    set @param = convert(char(20, @UserGUID )
    set @innersql = 'select ReportManager.dbo.UserGroupMembershipNames('+@param+')'
    set @sql = 'select * from openquery(ASILIVE,'' '+ @innersql +' '' )' 
    RETURN exec sp_executesql @sql
END

#4

Not the prettiest solution but it works if you can work around passing parameters into the linked-server function

这不是最漂亮的解决方案，但如果您可以将参数传递到链接服务器功能，它就可以工作

CREATE FUNCTION fn_LocalFunction
( 
   @SomeParamOfLinkedFunction VARCHAR(100)
)
RETURNS TABLE
AS
RETURN
    SELECT SomeField
    FROM OPENQUERY([YOURSERVER], 'SELECT * FROM [SOMEDB].dbo.fn_SomeRemoteFunction(NULL)') tst
    WHERE SomeCondition = @SomeParamOfLinkedFunction

#1