Using OpenQuery

The OPENQUERY command is used to initiate an ad-hoc distributed query using a linked-server. It is initiated by specifying OPENQUERY as the table name in the from clause. Essentially, it opens a linked server, then executes a query as if executing from that server.

While executing queries directly and receiving data directly in this way is not bad, there are implications when trying to join the results of OPENQUERY with a local table. Typically joining remote tables with local tables across the network is not the most efficient method of querying. In some cases it may be better to subscribe to a remote table by importing it locally, then joining it locally.

Setup Ad Hoc Distributed Queries

In order to utilize, a server-level configuration needs to be applied to the default configuration in order to allow ‘Ad Hoc Distributed Queries’.

In order to see the current setting, run the following:

EXEC sp_configure

If you see the config_value set to 1 (as the figure below), then the option is already set. Otherwise you need to change the configuration.

OPENQUERY_sp_configure_adHocdistributedqueries

Ad Hoc distributed queries turned on

To set the configuration, simply run the following code below:

-- Enable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH OVERRIDE
GO

OpenQuery Example

The next step is to find or define the link server required and specify the linked server name as the first parameter in OPENQUERY. This tutorial already assumes you have a linked server setup, so from here we execute the query. The execution is pretty straight forward. OPENQUERY is specified right after the from clause followed by the first parameter as the linked server, and the second parameter as the query to execute.

--****************************
--* Example 1
--* Import all users into new table
--*****************************/
SELECT *
INTO dbo.Users_Import
FROM OPENQUERY(remotelinkedservername, 'SELECT * FROM dbo.Users' )


--****************************
--* Example 2
--* Insert users that do not exist
--* into existing table
--*****************************/
INSERT INTO dbo.Users
(
    UserID,
    UserName,
    FirstName,
    LastName
)
SELECT
    UserID,
    UserName,
    FirstName,
    LastName
FROM OPENQUERY(remotelinkedservername, 'SELECT * FROM dbo.Users' ) ru
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Users
    WHERE UserID = ru.UserID
)


--/****************************
--* Example 3
--* Insert results from remote procedure
--* into new table
--*****************************/
SELECT *
INTO dbo.List
FROM OPENQUERY(remotelinkedservername, 'set fmtonly off exec dbo.GetList')

One comment
Thumie 26 Feb 2016 at 9:01 am

The example is very practical and straight to the point.

Well Done.

Featured Articles

 Site Author

  • Thanks for visiting!