Skip to main content

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

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. 
For example:

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.


Comments

  1. The last code snipet is INCORRECT that is not possible

    ReplyDelete
  2. Its a working sample. I have used it myself in SQL Server 2005.

    ReplyDelete
  3. Didnt work for me on SQL Server 2008

    ReplyDelete
  4. Remote function calls are not allowed within a function.

    ReplyDelete
  5. I get the error Remote function calls are not allowed within a function

    ReplyDelete
  6. 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
    Replies
    1. already set the RPC out, still don't work for SQL 2012. "Remote function calls are not allowed within a function."

      Delete
    2. This comment has been removed by the author.

      Delete
  7. 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.

    Sheikh M. Haris, did you try this in SQL Server version greater than 2005 ?

    ReplyDelete
  8. I also received the same error "Remote function calls are not allowed within a function."
    Running from SQL 2005, linked server is SQL 2014.
    RPC Out is already True.

    ReplyDelete

Post a Comment

Popular posts from this blog

Microsoft SQL Server 2005 Service Pack 3 for Windows 7 (64 bit)

You can download from here Microsoft SQL Server 2005 Service Pack 3 Overview Service Pack 3 for Microsoft SQL Server 2005 is now available. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP3. You can use these packages to upgrade any of the following SQL Server 2005 editions: Enterprise Enterprise Evaluation Developer Standard Workgroup Download Size: 326.0 MB http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en Microsoft SQL Server Management Studio Express Service Pack 3 Overview Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services. SSMSE can also manage instances of the SQL Server Database Engine created by any edition of SQL Server 2005. Note: SSMSE cannot manage SQL Server Analysis

Configuring CORS in IIS - Response to preflight request doesn't pass access control check: It does not have HTTP ok status

The Access-Control-Allow-Origin Header Explained – With a CORS Example Often times when calling an API, you may see an error in your console that looks like this: Access to fetch at 'http://somesite.com' from origin 'http://yoursite.com' has been blocked by CORS policy: The 'Access-Control-Allow-Origin' header has a value that is not equal to the supplied origin Add following in <system.webServer> <httpProtocol> <customHeaders> <add name="Access-Control-Allow-Methods" value="*" /> <add name="Access-Control-Allow-Origin" value="*" /> <add name="Access-Control-Allow-Headers" value="*" /> </customHeaders> </system.webServer> After adding the above code in web.config, received the following error in response. .... Has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: