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

Edit your question and include the query you are running. This might have nothing to do with the UDF call. — Gordon Linoff, Apr 20 '15 at 15:30
@Gordon Linoff , thanks for the reply. The query running fine from other server(original source) but when I am running the same query from linked server I got the above error. — GK1960, Apr 20 '15 at 15:32
Check this out: http://stackoverflow.com/questions/4125820/sql-server-how-to-call-a-user-defined-function-udf-on-linked-server — Stephan, Apr 20 '15 at 15:40
PSW_Local i believe is your linked server. There is no DB name specified in your statement. — XtremeBytes, Apr 20 '15 at 16:22

corky_bantam · Answer 1 · 2015-04-23T05:34:15.917

4

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.

edited Apr 23 '15 at 05:34

answered Apr 22 '15 at 07:04

corky_bantam

329
4
11

thanks for the reply, I will try this and let you know. Thanks again – GK1960 Apr 22 '15 at 13:28
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 – GK1960 Apr 22 '15 at 13:35

score 3 · Answer 2 · answered Nov 16 '17 at 13:04

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]

score 0 · Answer 3 · answered Jun 21 '17 at 15:43

0

The OpenQuery function works:

SELECT * FROM OPENQUERY([myservername\yyySQL2008R2], 
    'SELECT mydb.dbo.ElapsedWorkDays(''1/1/2017'',GETDATE()) ' );

I tested this and the syntax works.

answered Jun 21 '17 at 15:43

jlo-gmail

4,453
3
37
64

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

3 Answers3