Calling stored procedure or function on another server in SELECT

• Paul_J

  Hall of Fame

  Points: 3698

  More actions

  August 28, 2006 at 5:20 pm

  #115117

  Hi all,

  I'm using SQL Server 2000 and I want to call a stored procedure (or user defined function - whichever is possible and easier) that is on another SQL server.

  I have created a Link Server to the other database.  How can I do this?  I've tried the following with a user defined function:

  SELECT LinkServerName.DatabaseName.dbo.FunctionName(a.param1, a.param2)

  FROM Table1 a

  But this isn't working.  I get the following error:

  "The object name 'LinkServerName.DatabaseName.dbo.' contains more than the maximum number of prefixes. The maximum is 2."

  Any ideas?

   

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  August 28, 2006 at 5:42 pm

  #657221

  AFAIK, you cannot do this with functions. Here;s an arcicle from MS with a workaround : http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319138

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• Paul_J

  Hall of Fame

  Points: 3698

  More actions

  August 28, 2006 at 5:49 pm

  #657222

  Thanks.

  How about stored procedures then?  I can do it using EXEC sp, but I need to call it inside a select...