Configuring Linked Servers in SQL Server

In the sql server to run distributed query, you have to configure linked server.

Basically linked server configuration allows Sql Sevrer to execute commands against OLE DB data sources on different servers. Through linked server you can access Remote server,issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.The ability to query diverse data sources similarly.

You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.

Using SQL Server Enterprise Manager:
Open SQL Server Enterprise Manager application, expand the object tree under the connected sql server for that linked server is created, expand the Server Object nod and than right click on Linked Server nod and choose new linked server option from the shortcut menu and follow the steps.

Using SPs: following list of Sps are used for managing linked servers.

1. sp_linkedservers - Shows information of all the linked servers.
2. sp_addlinkedserver - used for creating linked server.

Like:

USE master;
GO
EXEC sp_addlinkedserver
'Server Name',
N'SQL Server'
GO

3. sp_addlinkedsrvlogin - Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.

LIke:

EXEC sp_addlinkedsrvlogin 'AccountsServer', 'false', 'Domain\username', 'Username', 'password'

Let me give you complete example:

Suppose I want to connect to a database 'RnD' on the sql server 'HEX_SANJAY\HEXSANJAY' with the username 'sa' and password 'kkak'.
Execute the following code with your values for all the above mentioned settings to setup linked server.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'HEXSANJAY',
@srvproduct=N'SQL',
@provider=N'SQLNCLI',
@datasrc=N'HEX_SANJAY\HEXSANJAY',
@catalog=N'RnD'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'HEXSANJAY',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'kkak'
GO
EXEC master.dbo.sp_serveroption @server=N'HEXSANJAY', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HEXSANJAY', @optname=N'rpc out', @optvalue=N'true'
GO


4. sp_dropserver - Removes a server from the list of known remote and linked servers on the local instance of SQL Server.

Like : sp_dropserver 'ACCOUNTSSERVER', 'droplogins';