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

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

I am trying to attach a database to SQL 2005. This database has not previously been attached. I have only just installed SQL. I get the following message: Unable to open physical file "C:\ArrowSQL\Arr@Data\Arrow_data.mdf" Operating system error 5: "5(error not found)" (Microsoft SQL Server: Error 5120)". I have loaded SQL and the database fiel and directory with the same user acccount which is a local adminstrator ont hsi machine. I have checked that I have read/write access to the file. The machine runs Windows Vista Business. SQL has SP 2 loaded. Use the below script to find the sql service account Code Snippet declare  @sqlser  varchar ( 20 ) EXEC  master .. xp_regread @rootkey = 'HKEY_LOCAL_MACHINE' , @key = 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER' , @value_name = 'objectname' ,  @value = @sqlser  OUTPUT PRINT   'Account Starting SQL Server Service:'  + convert ( varchar ( 30 ), @sqlser ) After getting the servi

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: