from SQL Server 2000 Books Online (look for remote stored procedures):How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)To set up a remote server to allow the use of remote stored procedures Run the following code on the first server running Microsoft® SQL Server™: EXEC sp_addlinkedserver ServerName1, N'SQL Server' EXEC sp_addlinkedserver ServerName2 EXEC sp_configure 'remote access', 1 RECONFIGURE GOStop and restart the first SQL Server. Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication. -- The example shows how to set up access for a login 'sa' -- from ServerName1 on ServerName2. EXEC sp_addlinkedserver ServerName2, local EXEC sp_addlinkedserver ServerName1 EXEC sp_configure 'remote access', 1 RECONFIGURE GO -- Assumes that the login 'sa' in ServerName2 and ServerName1 -- have the same password. EXEC sp_addremotelogin ServerName1, sa, sa GOStop and restart the second SQL Server. Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.
EXEC sp_addlinkedserver ServerName1, N'SQL Server'
EXEC sp_addlinkedserver ServerName2
EXEC sp_configure 'remote access', 1
RECONFIGURE
GOStop and restart the first SQL Server.
Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication.
-- The example shows how to set up access for a login 'sa'
-- from ServerName1 on ServerName2.
EXEC sp_addlinkedserver ServerName2, local
EXEC sp_addlinkedserver ServerName1
EXEC sp_configure 'remote access', 1
RECONFIGURE
GO
-- Assumes that the login 'sa' in ServerName2 and ServerName1
-- have the same password.
EXEC sp_addremotelogin ServerName1, sa, sa
GOStop and restart the second SQL Server.
Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.