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 

Answer 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

Answer 4

See Call User-defined Function on Linked Server :SQL Server, which is my blog.

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

Answer 5

Please have a try on this, this will work on SQL Server 2005 onwards i think

exec ('SELECT [dbname].dbo.function_name()') AT [LINKED_SERVER_NAME]