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.
| 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 1The 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 ENDCREATE 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 ENDServer: Msg 117, Level 15, State 1, Line 1The 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. |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
|
|
How To Call A UDF From Different Server ?