How to call a UDF in master database on a linked server

Question

I am unable to call a UDF in a Linked Server.

Here's my T-SQL code:

DECLARE @Phone_Digits_Input varchar(12);
DECLARE @Phone_Min_Max_Input varchar(5);
DECLARE @Phone_Digits_String varchar(10);
DECLARE @Phone_Min_Max_String varchar(5);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @phone_digits varchar(10);

SET @Phone_Digits_String = 'phone_digits';
SET @Phone_Min_Max_String = '10-10';

SET @SQLString = N'SELECT @phone_digitsOUT = RemoteServer.master.dbo.myUDF(@Phone_Digits_Input, @Phone_Min_Max_Input)';
SET @ParmDefinition = N'@Phone_Digits_Input varchar(10), @Phone_Min_Max_Input varchar(5), @phone_digitsOUT varchar(10) OUTPUT';

EXECUTE RemoteServer.master.dbo.sp_executesql @SQLString, @ParmDefinition, @Phone_Digits_Input = @Phone_Digits_String,
     @Phone_Min_Max_Input = @Phone_Min_Max_String,
    @phone_digitsOUT=@phone_digits OUTPUT;
SELECT @phone_digits;

When I run it, I get a message:

Msg 344, Level 16, State 1, Line 1
Remote function reference 'RemoteServer.master.dbo.myUDF' is not allowed, and the column name 'RemoteServer' could not be found or is ambiguous.

How can I fix the issue please?

Have you tried removing the server name from the string (change "RemoteServer.master.dbo.myUDF(" to "master.dbo.myUDF(". Because your EXECUTE includes the remote server name, you should be OK — Brian Stork, Sep 02 '15 at 19:45

score 0 · Answer 1 · answered Sep 03 '15 at 20:23

I got the server name wring! The UDF was i a different database! Sorry for my confusion. The final code is below:

DECLARE @Phone_Digits_Input varchar(12);
DECLARE @Phone_Min_Max_Input varchar(5);
DECLARE @Phone_Digits_String varchar(10);
DECLARE @Phone_Min_Max_String varchar(5);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @phone_digits varchar(10);

SET @Phone_Digits_String = 'phone_digits';
SET @Phone_Min_Max_String = '10-10';

SET @SQLString = N'SELECT @phone_digitsOUT = dbo.MyUDF(@Phone_Digits_Input, @Phone_Min_Max_Input)';
SET @ParmDefinition = N'@Phone_Digits_Input varchar(10), @Phone_Min_Max_Input varchar(5), @phone_digitsOUT varchar(10) OUTPUT';

EXECUTE MyServer.MyDB.dbo.sp_executesql @SQLString, @ParmDefinition, @Phone_Digits_Input = @Phone_Digits_String,
       @Phone_Min_Max_Input = @Phone_Min_Max_String,
       @phone_digitsOUT=@phone_digits OUTPUT;
SELECT @phone_digits;

Thank you.

How to call a UDF in master database on a linked server

1 Answers1