Create Linked Server in Sql Server 2008 / Insert from one Db to another in Sql Server 2008

Linked Servers can be used to connect databases on remote server .

Suppose in the production environment if you have two servers on the network and we need to trigger insert in a table of one database to a table in different database , we can use Linked Servers.

We can create Linked Server using the Stored Procedure “sp_addlinkedserver” .To execute this , we must have the admin privileges. One has to login into sql server management studio with “Sa” user.

exec sp_addlinkedserver     @server = 'MyLinkedServer', -- LINKED SERVER NAME (ANYTHING)     @srvproduct = '' ,     @provider = 'SQLNCLI', -- SQL SERVER PROVIDER .. SO SQLNCLI     @datasrc ='SERVER=11.11.1.1', -- REMOTE DATABASE SEREVR IP ADDRESS     @location = '' ,     @provstr = '',     @catalog = 'Test' -- DATABASE ON REMOTE SERVER                   OR exec sp_addlinkedserver 'MyLinkedServer','','SQLNCLI','SERVER=11.11.1.1','','','Test'

We can also create Linked Server from Sql Server Management Studio by following these steps :

Click Start, click All Programs, click Microsoft SQL Server 2008, and then click SQL Server Management Studio.

In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect with Sa login.

In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

                                                                                                                                          

In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Server you want to link to.

Under general , Choose the following things :

In the left pane of the New Linked Server dialog,  choose Security and provide remote user name and password .

You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

In the left pane of the New Linked Server dialog, choose Server Options.   Set the Rpc and Rpc Out parameters to True and then click OK .

To verify Linked Server is created or not :

using the Stored Procedure sp_linkedservers

                                                   exec sp_linkedservers

                                                                 OR

1) In the object explorer , expand Server Objects , expand Linked Servers . Now verify for the linked server created by you.

2)To test the connection , right click on the linked server created and select Test Connection .  If connection is successful , then you can perform all DML operations on the tables in the remote database.

Querying Linked Server :

SELECT * FROM [LinkedServerName].[RemoteDatabaseName].[DBOwner].TableName

Similarly use Insert , Delete and Update queries. We can call functions , execute SPs etc.

Delete / Drop Linked Server :

1) In the object explorer , expand Server Objects , expand Linked Servers . Right Click on the Linked Server which you wish to delete and click Delete.

2)Drop Linked Server using Stored Procedure “sp_dropserver”

                          EXEC sp_dropserver MyLinkedServer