Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.