Executing DML on remote database server using Linked Server/Openrowset

Hello friends, in my last article we saw how we can fetch data from remote database server using Openrowset() / Linked Server, today we are going to learn how we can execute DML (Insert, Update, Delete) statements on remote database server using Openrowset() / Linked Server.

Delete using Openrowset():- To delete rows from a table which is residing on a remote database server, we can use below syntax to perform this task.

delete  from Openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote','select * from emp where eid=106')

 

The above SQL statement will delete the rows from “Emp” table residing on remote database server those will be returned by Openrowset()function.We have only one employee which is having eid=106, hence one row will be deleted.

We can see in the above screenshot, 1 row affected message is being shown in the result window.

 

Update using Openrowset():- To update rows in a table residing on a remote database server, we can use below syntax to perform this task.

update openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote',

'select * from emp where eid=107')

set name='Rohit Sinha'

The above query will update the ”Name” column of the “Emp” table where eid=107.

Insert using Openrowset():- To insert rows in a table residing on a remote database server, we can use below syntax to perform this task.

 

insert openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote',

'select * from emp where eid=107')

values(115,'priyanka arora')

 

The above SQL statement will insert one row into “Emp” table, It means, we just need to include table name in the select query in which we want to insert the rows.

 

DML support in linked Server:- We saw above, how we can use Openrowset() function to execute DML(Insert, Update, Delete), in the same manner we can use linked server to execute DML statements on remote database server. Below is the syntax for the same.

 

Delete using linked Server:-

delete from openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=115')

 

Update using linked Server:-

update openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=107')

set name='Akash Rajput'

 

Insert using Linked Server:-

insert openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=107')

values(115,'Mohan Sharma')

 

This is all about linked server and Openrowset() function, I hope you will find this article useful for you. Please feel free to ask any questions related to this article.

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.

How to get data from remote database using T-SQL / Openrowset function/ Linked Server

Hello friends, today I am going to talk about a very common topic that is asked quite frequently from many database developers and the question is that “How we can get data from remote Server using T-SQL“, There are two ways of fetching data from remote server in SQL which are following.

• Using Linked Server
• Using Openrowset() Function

I picked this topic as many people know about the first method of fetching data from remote server but the second option is not very popular. Hence I thought to share this knowledge with like minded people around the world. In this article I will talk about only one method which is using “Openrowset()” function.

I have created one table “Emp” on remote server and one more table on with same name at local server using below SQL Statement.

----Query needs to be run on remote server.

Create table Emp(Eid int,Name Varchar(50))

insert into Emp values(106,'Neeraj Yadav')

insert into Emp values(107,'Arvind Parashar')

insert into Emp values(108,'Vidushi Pandey')

insert into Emp values(109,'Shivika Garg')

insert into Emp values(110,'Pranav Kumar')

---Query needs to be run on local server.

Create table Emp(Eid int,Name Varchar(50))

insert into Emp values(101,'Neeraj Yadav')

insert into Emp values(102,'Neha Nagpal')

insert into Emp values(103,'Akshay Sinha')

insert into Emp values(104,'Ankit Sharma')

insert into Emp values(105,'Rachit Sharma')

Problem Statement: - We have 5 records in each table, in remote database we have employee id starting from 106 to 110 and in our local database we have employee id starting from 101 to 105.

Now I want to fetch all five records from remote database and insert into the local database table.

Solution:- Below is the Syntax of Openrowset() function to fetch the data from remote database server. Let’s try to understand the below SQL Statement.

select *

from openrowset('SQLNCLI','Server=Neeraj-PC\server2008;uid=sa;pwd=Mind1234;database=Demo_Remote','select * from emp')

Openrowset() Function description:-

select *

from Openrowset('[Provider_Name]','Server=[Remote Server(SQL)or Instance Name];uid=[User ID];pwd=[Password];database=[Remote Database],'[Select Query]')

Provider_Name à [SQLNCLI] (If remote Server is SQL Server)

Serverà  [Neeraj-PC\Server2008] (Instance name of SQL Server)

Uidà [Sa] (User Id of remote SQL Server)

PWDà [Mind1234] (Password of remote SQL Server)

Databaseà [Demo_Remote] (Database name of remote Server)

Select Query à (Select * from EMP)

Lets Execute the above statement and see the results.

 We can see that we are getting below error message while executing the query.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

We are getting this error message as distributed ad Hoc queries are blocked  In SQL server. To enable this feature we will execute below SQL statements on the local Server.

sp_configure 'show advanced options', 1 reconfigure with override

go                                 

sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure with override

After executing the above query we will be able to execute Openrowset() function, lets execute the function and see the results.

We can see in the above screenshot, we are able to fetch the records from “Emp” table residing on the remote server. Now we can insert fetched records into our local database table using below SQL statement.

Insert into Emp(Eid,Name)

select *

from openrowset('SQLNCLI','Server=Neeraj-PC\server2008;uid=sa;pwd=Mind1234;database=Demo_Remote','select * from emp')

I hope after reading this article, you will have an alternative way of Linked server to fetch the data from remote database server.