Fetch data from remote server using OPENROWSET in SQL Server

We are fetching the data from table in database with in server or fetching data from different database with in server. But sometimes we require fetching data from different database which is not in same server but it is in different server.

For different database but with in same server we can get records easily by “.(dot)” like:

SELECT * FROM DatabaseName.Schema.Table

Now let see how we can get the records from table from database which is not in same server and it is situated at different server.

This can be possible by the Linked Server also. However if we do not want to create link server and need to get the data then we have option to use “OpenRowSet”.

There are three parameters use in OpenRowSet which are:

• Provider
• Server (IP Address) with credentials (username/password) and database
• Query (which is going to be executed with that server’s database)

Here is the syntax use to get data by OpenRowSet

OPENROWSET('SQLNCLI','Server=serverIP;Uid=UserName;Pwd=password;Database=DBName;','SELECT * FROM tablename')

Before executing this query we need to set some option which is requires to run the OpenRowSet query.

Set this option:

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO;

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO;

After setting these option run the below query with proper required details:

SELECT

      FS.*

FROM OPENROWSET('SQLNCLI','Server=serverIP;Uid=UserName;Pwd=password;Database=DBName;','SELECT * FROM tablename') AS FS;

This will get the records from table which is not on same server but from remote server which is created in database on given Server.

This way it works without creating Linked Server  or without using the Import/Export option which already given to export the data from one server to other server by specifying the source and destination.

We can import/export the data from one database server to other database server’s table by:

Right click on database >> Go to Task >> Go to Import/Export

Hope this helps…..