Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How To Call A UDF From Different Server ?

Author  Topic 

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-30 : 03:14:43
Hi all :
Can anybody tell me how to call a udf function from 1 server into another.
For eg : the Function is in Maximus DB of Production1 server and I want to call it in Production2 Server.
I have added a linked server as Production1 in Production2 and while calling the function I specify the complete statement as Production1.Maximus.dbo.GetProductName() but I get an error saying :


Server: Msg 117, Level 15, State 1, Line 1
The object name 'PRODUCTION1.MAXIMUS.DBO' contains more than the maximum number of prefixes. The maximum is 2.


Thanks
-Marjo.

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-31 : 05:12:02
Hi,

My first Approach that failed:
I created GetName() function in production1 and used OpenQuery.

CREATE FUNCTION GETNAME(@ID VARCHAR(15)) ===>>>> IN PRODUCTION 1 SERVER
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @NAME VARCHAR(20)
DECLARE @PREPSQL VARCHAR(200)
SET @PREPSQL = 'SELECT @NAME = FULLNAME FROM OPENQUERY(PRODUCTION2,''SELECT FULLNAME FROM EMP_MAIN WHERE ID=' + @ID + ''')'
EXECUTE(@PREPSQL)
RETURN @NAME


=> This function does not work, it says Invalid use of EXECUTE within a function. Then how to EXECUTE @PrepSql ?
Secondly, I want to store the Fullname returned by sql in the variable @Name but this will again not work bcoz it searches for the variable @Name in Production2 linked server.

My Second Approach that too failed :
I created a function GETNAME() in Production1 Server which I want to call in GETEMPNAME() function of Production2. I have added a linked server Production1 in Production2.

CREATE FUNCTION GETNAME(@ID VARCHAR(15)) =>>> IN PRODUCTION1 SERVER
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @NAME VARCHAR(20)

SELECT @NAME = PRODUCT_NAME FROM MAXIMUS.DBO.EMP_MAIN WHERE ID = @ID
RETURN @NAME

END


CREATE FUNCTION GETEMPNAME(@ID VARCHAR(15)) =>>> IN PRODUCTION 2 SERVER
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @NAME VARCHAR(20)
SELECT @NAME = PRODUCTION1.MAXIMUS.DBO.EMP_MAIN.GETNAME(@ID)
RETURN @NAME
END


Server: Msg 117, Level 15, State 1, Line 1
The object name 'PRODUCTION1.MAXIMUS.DBO' contains more than the maximum number of prefixes. The maximum is 2.

Merkin, I have tried my best but still I didn't find any solution. Therefore I am posting both the approaches that I have used.
I would really appreciate for any help or suggestions.

Best Regards,
- Marjo.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-31 : 06:20:54
Hi Marjo

It appears that you can't do this.
I did find a KB article with some workarounds here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138

Or, could you use a stored proc on the linked server ?


Damian
Go to Top of Page

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-31 : 07:02:22
Thanks Merkin for replying. I tried changing the function but don't know what to pass in RETURN.

CREATE FUNCTION GETEMPNAME(@ID VARCHAR(15)) =>>> IN PRODUCTION 2 SERVER
RETURNS VARCHAR(20)
AS
BEGIN
EXEC PRODUCTION1.MAXIMUS.DBO.SP_EXECUTESQL 'SELECT MAXIMUS.DBO.GETNAME(@INPUTVAR)', '@INPUTVAR VARCHAR(20)', @INPUTVAR = @ID
RETURN ==>> WHAT ?
END


- Marjo.
Go to Top of Page

trusted4u
Posting Yak Master

109 Posts

Posted - 2003-10-31 : 07:55:43
quote:
Or, could you use a stored proc on the linked server ?

Merkin, just now I tried creating a sp in production1 and calling it in GetName() function of production2 but it gave me an error :
Remote function calls are not allowed within a function.

And when I execute this sp from Query Analyser it works perfectly. Then Y not in Function ?

Thanks
-Marjo.
Go to Top of Page
   

- Advertisement -