Friday, November 2, 2012

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

6 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. var emailRegex = new RegExp(/^(("[\w-\s]+")|([\w-]+(?:\.[\w-]+)*)|("[\w-\s]+")([\w-]+(?:\.[\w-]+)*))(@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$)|(@\[?((25[0-5]\.|2[0-4][0-9]\.|1[0-9]{2}\.|[0-9]{1,2}\.))((25[0-5]|2[0-4][0-9]|1[0-9]{2}|[0-9]{1,2})\.){2}(25[0-5]|2[0-4][0-9]|1[0-9]{2}|[0-9]{1,2})\]?$)/i);
    if (emailRegex.test($('.email').val().trim())) {
    $('.email').removeClass('error');
    isEmail = true;
    }
    else {
    $('.email').addClass('error');
    }

    ReplyDelete