SQL Sever 7.0 allows developers to execute commands against OLE DB data sources on different servers. In order to execute commands on remote servers, the SQL Server instance where the commands will be issued must be setup properly. This entails adding the remote server to SQL Server’s linked server list. Do this by using the sp_addlinkedserver command.
For example, to link a remote SQL Server database that resides on the RemoteDBServer server, you would use the following syntax:
EXEC master. . sp_addlinkedserver @server = 'RemoteDBServer', @provider = 'SQLOLEDB', @srvproduct = 'SQL Server',@datasrc = 'RemoteDBServer'
Note that only members of the sysadmin role can set this server option.
Once the remote database has been linked, queries can be executed against it—as long as the remote tables are prefaced using Server.Database.Table Owner.Table Name. For example, the following query would return all rows in the authors table of our RemoteDBServer SQL Server database:
SELECT * FROM RemoteDBServer.pubs.dbo.authors