Programming, SQL, SQL Server 2008, Technology

Passing Variables to User Defined Functions (UDF) Across a Linked Server (SQL Server)

User defined functions are a useful tool when working with SQL Server. However recently as Luvy and I were working on creating a stored procedure we found out that user defined functions cannot be executed across a linked server in the usual way. We ran our tests on SQL Server 2008, but I believe this issue is applicable in SQL Server 2000 and 2005 as well.

The specific problem we encountered was that we wanted to select data from a user defined function via the linked server into a temporary table while passing variable parameters to the function.

After some trial and error we discovered two ways that one can execute user defined functions via a linked server. One can use the command: Openquery or one can use the command sp_executesql

We first tried using the command Openquery, but encountered roadblocks when when passing in parameter variables.

Then when we tried using the sp_executesql command, which ended up working like a charm.

Below is an example of the sp_executesql command we ran cross server using two parameter variables called Field1 and Field2.

declare @Field1 char(4)
declare @Field2 char(8)
set @Field1 ='1234'
set @Field2 = '567'

)exec CROSSSERVER.databasename.dbo.sp_executesql N'
SELECT @Field1,@Field2,* 
from databasename.dbo.dbFnc(@FromDate,@ToDate,@Charge)',
N'@Field1 char(4),@Field2 char(8),
@FromDate smalldatetime,@ToDate smalldatetime,
@Charge money',@Field1=@Field1,

select * from #TheTempTable
drop table #TheTempTable



7 thoughts on “Passing Variables to User Defined Functions (UDF) Across a Linked Server (SQL Server)”

  1. i need help as i am facing this problem. when i pass 2 output parameter to a function through a linked server I am getting this error:
    for 1 out parameter its working fine but when i use two output parameters it gives this error:

    “Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

    Here is the query:


    EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
    AT linkserver_name;
    waiting for the reply.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s