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' INSERT INTO #TheTempTable (Field1, Field2, Amt, [Year], [Month] )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, @Field2=@Field2,@FromDate=@FromDate,@ToDate=@ToDate, @Charge=@Charge select * from #TheTempTable drop table #TheTempTable
References:
Justin Cooney 
I do love the manner in with you have presented this problem.
This was a fuzzy one… thanks for documenting it. It is good reference for special cases when dealing with across server variables.
Hi, could I quote some of the information from this blog if I reference you with a link back to your site? Cheers
Hi, yes that works. Cheers!
Excellent blog, I am going to spend more time reading about this subject
Thank you, I’m glad you found it to be useful.
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:
DECLARE @RET INT
EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
, @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
, @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE
, @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
AT linkserver_name;
waiting for the reply.