Call a sproc from another sproc on a different machine

Question

I am using SQL server 2000. I have a stored procedure sitting on machine A. I want to call this stored procedure from within a stored procedure on machine B. How do I go about this?

Answer 1

Read this blog post. Essentially, if the server is already set up as a linked server you can use EXEC RemoteServer.DatabaseName.DatabaseOwner.StoredProcedureName

Answer 2

You could use the Linked Server feature of SQL Server.

A linked server configuration allows Microsoft SQL Server to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access

• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

• The ability to address diverse data sources similarly.

A call could look like this:

  exec MyRemoteServer.MyDB.dbo.sp_MyStoredProc