CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqllinked-server
GK1960
GK1960

Reputation: 121

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

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

Upvotes: 5

Views: 24516

Answers (3)

Adam
Adam

Reputation: 4580

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]

Upvotes: 3

jlo-gmail
jlo-gmail

Reputation: 5038

The OpenQuery function works:

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

I tested this and the syntax works.

Upvotes: 0

corky_bantam
corky_bantam

Reputation: 339

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.

Upvotes: 5

Related Questions