Call User-defined Function on Linked Server :SQL Server

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a "four-part naming" convention (server.database.dbo.Function), you may receive error message. 

The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. Thats why error message indicates that the syntax of a Transact-SQL statement is incorrect. 

To work around this problem, use the Openquery function instead of the four-part naming convention. For example, instead of the following query

?

1	Select * from Linked_Server.database.dbo.Function(10)

run a query with the Openquery function:

?

1	Select * from Openquery(Linked_Server,'select database.dbo.Function(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:

?

1 2	exec Linked_Server.database.dbo.sp_executesql N'SELECT database.dbo.Function(@input)',N'@input int',@input=10

but if you want to use it in a select statement (within some Stored Procedure), then this EXEC command will create some problems for you :)

However this could be resolve easily by creating a user-defined function in your database (not the linked server one)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

In order to run the above function, you have set RPC Out value of that particular linked server to be True.