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
Select * from Linked_Server.database.dbo.Function(10)
run a query with the Openquery function:
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.
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)
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.
The last code snipet is INCORRECT that is not possibleReplyDelete
Its a working sample. I have used it myself in SQL Server 2005.ReplyDelete
Didnt work for me on SQL Server 2008ReplyDelete
Remote function calls are not allowed within a function.ReplyDelete
I get the error Remote function calls are not allowed within a functionReplyDelete
In order to run the above function, you have set RPC Out value of that particular linked server to be True. Check the attached image in the end.ReplyDelete
already set the RPC out, still don't work for SQL 2012. "Remote function calls are not allowed within a function."Delete
This comment has been removed by the author.Delete
I get an error "Remote function calls are not allowed within a function." . I am running this in SQL Server 2016 and setup my linked server's RPC Out to True.ReplyDelete
Sheikh M. Haris, did you try this in SQL Server version greater than 2005 ?
I also received the same error "Remote function calls are not allowed within a function."ReplyDelete
Running from SQL 2005, linked server is SQL 2014.
RPC Out is already True.