How to fetch data from remote database server using Linked Server

Hello friends, in my last article, we saw how to fetch data from remote database server using Openrowset() function, now we will talk about the second method of fetching data from remote database server i.e. using Linked Server. Lets see first, how to create Linked server using wizard.

We will follow below navigation path to create Linked Server using management Studio(wizard).

Login to SQL ServeràServer ObjectsàLinked ServersàRight click on Linked Servers folderàClick On New Linked Server Menu

You can refer below screenshot to follow the navigation path.

As soon as we click on “New Linked Server” menu, a new window will pop up as shown in the below screenshot.

We can create Linked server between two different data source as well, but in this article I am taking example of same data sources (both sources are SQL). In the above screenshot, I have indicated two fields using two arrows, 1^st field is “Linked Server” where we need to provide the instance name of the remote server. In my case instance name of remote server is (Neeraj-PC\Server2008) and 2^nd field is to specify which type of source is our remote Server. In my case, it is SQL Server, hence I have opted the option SQL Server. On the left side of the screen, there is a menu(Security), we will click on the menu(security) and the current screen will be transformed into different screen(As shown in the below screenshot).

As shown in the above screenshot, we can see the “Add” button(encircled by red color), we will click on “Add” button to map the local server login with remote server login. Clicking on “Add” button will add a row as shown in the below image.

Once the row is added as shown in the above screenshot, we will click on the combo box of Local Login field that will populate all the logins present in local database server. For mapping the login there are two ways.

Impersonation(Only for windows login):- We can impersonate the local Server’s windows login by checking the checkbox “impersonate” as shown in the above image. It means if a user login to the SQL server using the windows login which is mapped for impersonation that user will be able to access the remote server database.

I have selected the windows login( Neeraj-PC\Neeraj) and checked the “impersonate” check box. Then we will select  the option(“Be made using the login’s current security context”) encircled with red color and click on “OK” button, that will create a linked server object between local database server and the remote database server.

Without impersonation :- We can also map logins without impersonation. In that case we will select the SQL login(Sa or any other created login) of the local database server and map that login with the remote database server login and password as shown  in the below screenshot.

Then we will select the option “Be made using the login’s current security context” encircled with red color and click on “OK” button. Now we are done with the creation of Linked server and ready to use it for fetching data from the remote server.

To fetch the data from remote Server we need to use “Openquery()” function, below is the Syntax to fetch the records from the remote Server.

select * from Openquery([Neeraj-PC\Server2008],'Select * from Demo_Remote.dbo.Emp')

Openquery():- Openquery() function takes two parameters, first parameter is the name of the linked Server and the second parameter is the query that we want to run on the Remote database server.

Note:- Query should be using fully qualified names of the object.i.e.

(databasename.owner.tablename)in our case database name is “Demo_Remote”, owner is dbo and table name is “Emp” hence we have written(Demo_table.dbo.Emp).

Lets run the query and see the results.

We can see in the above screen shot, we are getting the results from the remote database server. I hope you will find this article useful. In my forthcoming articles we will see how to execute (DML) commands on remote server using Linked server and Openrowset() function.