SQL Server: How to call a user-defined function (UDF) on linked server?

Question

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

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)

run a query with the Openquery function:

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:

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?

Answer 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.

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"

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'

Answer 2

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