Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)

SQL Server 2000

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

  1. 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
    GO
    
  2. Stop and restart the first SQL Server.

  3. 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
    GO
    
  4. Stop and restart the second SQL Server.

  5. Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.

See Also

sp_addremotelogin

sp_configure

sp_addlinkedserver

RECONFIGURE

Show:
© 2015 Microsoft