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.
No comments:
Post a Comment