How can I call Scalar user defined function from linked server?

Question

I have to run a query from linked server (SQL Server 2008) that contains Scalar UDF (with one parameter) on that server, when I run the query I got following error

Cannot find either column "PSW_Local" or the user-defined function or aggregate "PSW_Local.dbo.udf_psw_local_format_name", or the name is ambiguous.

I followed someones suggestion on the internet using OPEN Query but it's not working.

Can anybody suggest different method? Thanks in advance

Solution 1

To reference a linked server you must use 4-part naming for the object that you wish to reference on the linked server.

This is composed of [SERVER_NAME].[DATABASE_NAME].[SCHEMA_NAME].[OBJECT_NAME]

Here's a nice little article that explains it - http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/

Make sure that SERVER_NAME is the name given to the server on the local machine. (I assume that the connection to the linked server has been tested.)

For referencing a function on a linked server you do indeed need to use OPENQUERY. The code should look something like this:

SELECT * FROM OPENQUERY([LINKED_SERVER_NAME],'SELECT [DATABASE_NAME].[SCHEMA_NAME].FUNCTION_NAME()')

Note that if you use a string as an argument for the function then you will need to double quote it.

Solution 2

If you need to pass in arguments and return a value then you can use something like this;

DECLARE @Arg1 INT = 1234;
DECLARE @Arg2 VARCHAR(15) = 'ABCD';
DECLARE @ReturnValue INT;
DECLARE @Parameters NVARCHAR(255) = '@ReturnValue INT OUTPUT'


DECLARE @Command NVARCHAR(MAX) = 
    N'SELECT @ReturnValue = [ReturnValue] FROM OPENQUERY(
        [LINKED_SERVER]
        , ''SELECT [Database].[schema].[ScalarValuedFunction](' + CAST(@Arg1 AS VARCHAR(255)) + ', ''''' + @Arg2 + ''''') ReturnValue''
        )'

PRINT @Command

EXEC sys.sp_executesql 
    @Command, @Parameters, @ReturnValue=@ReturnValue OUTPUT

SELECT @ReturnValue [ReturnValue]

Comments

[GK1960]

thanks for the reply, I will try this and let you know. Thanks again

[GK1960]

Hi @corky_bantam, i have just tried but got error 'Remote function reference is not allowed'. I have tried both Linked server name and [SERVER_NAME]. any idea Thanks