Add A Linked Server In SQL Server

The system stored procedure sp_addlinkedserver can be used to created a linked server represent a remote SQL Server. This also can be used when SQL query needs to access multiple databases located in different servers.

Only following parameters are essential:

EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;

If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.

To establish the access:

EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';

This would created linked server for all database users. When use it, just like this:

SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO


http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx