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:

[cc lang=”sql”]
EXEC sp_configure
[/cc]

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.

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

[cc lang=”sql”]
— Enable Ad Hoc Distributed Queries
EXEC sp_configure ‘Ad Hoc Distributed Queries’,1
RECONFIGURE WITH OVERRIDE
GO
[/cc]

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.

[cc lang=”sql”]
–****************************
–* 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’)
[/cc]