Edit

Compare query remote execution options

  • Article

In SQL Server, there are three ways to execute a query remotely:

  • OPENQUERY
  • OPENROWSET
  • EXECUTE AT

This article describes these three methods.

OPENQUERY

OPENQUERY (Transact-SQL)

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. In a query, use OPENQUERY in the FROM as if it were a table name. You can also reference OPENQUERY as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

OPENQUERY requires a pre-added and configured linked server and a request text to a remote server. OPENQUERY does not require a four part name convention to access objects.

OPENROWSET

OPENROWSET (Transact-SQL)

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, instead consider using linked servers, PolyBase, or direct connections between the two data sources via tools like SQL Server Integration Services (SSIS) or custom applications.

In a query, use OPENROWSET in the FROM clause of a query. You can also use OPENROWSET as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

For additional information, OPENROWSET use an explicitly written connection string.

EXECUTE AT

EXECUTE (Transact-SQL)

Allows dynamic SQL to run against a linked server. One of the parameters of the EXECUTE call is AT, which is designed to bypass the OPENQUERY and OPENROWSET restrictions. EXECUTE (``<query>``) AT [<linked server>] is dynamic SQL that can return any number of result sets from a remote server.

Guidance for dynamic SQL

Avoid the use of dynamic SQL commands in applications, and restrict the permissions on users with access to dynamic SQL commands. Constructing queries to execute via EXECUTE can create vulnerabilities to websites and applications via SQL Injection attacks. For more information, see SQL Injection.

When performance is a concern, test remote queries:

  • Ensure that as much logic as possible is executed on the remote server
  • Verify indexes on the remote server index tables appropriately to support the query
  • Be aware that the use of remote queries in a codebase complicates database code version control and maintenance of development and test environments

Examples

A. Execute a SELECT pass-through query with OPENQUERY

The following example uses a pass-through SELECT query to select the rows with OPENQUERY:

SELECT * 
    FROM OPENQUERY ([linkedserver], 
        'SELECT * FROM AdventureWorksLT.SalesLT.Customer');

B. Execute a SELECT pass-through query with OPENROWSET

The following example uses a pass-through SELECT query to select the rows with OPENROWSET

SELECT a.*
FROM OPENROWSET('MSOLEDBSQL', [linkedserver],
     'SELECT * FROM AdventureWorksLT.SalesLT.Customer') AS a;

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.

C. Execute a SELECT pass-through query with EXECUTE AT

The following example uses a pass-through SELECT query to select the rows with EXECUTE ... AT

EXECUTE ('SELECT * FROM AdventureWorksLT.SalesLT.Customer') AT [linkedserver]

D. Execute multiple SELECT statements

The following example uses a pass-through SELECT query and getting multiple result sets

EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer;
    SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.CustomerAddress;') AT [linkedserver];

E. Execute a SELECT and pass two arguments

The following example uses a pass-through SELECT with two arguments

EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', 10, 'Garza') AT [linkedserver];

F. Execute a SELECT and pass two arguments, using variables

The following example uses a pass-through SELECT with two arguments by using variables

DECLARE @CustomerID AS INT
DECLARE @LastName AS VARCHAR(100)
SET @CustomerID = 10
SET @LastName = 'Garza'
EXECUTE ('SELECT TOP 10 * FROM AdventureWorksLT.SalesLT.Customer 
WHERE CustomerID = ? AND LastName = ?', @CustomerID, @LastName) AT [linkedserver];

G. Execute DDL statements with EXECUTE using linked servers

The following example uses a DDL statement on Linked Server

EXECUTE (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1
CREATE TABLE dbo.Table1
(
    Column1 INT
)' ) AT [linkedserver];

Once you are done with your testing, clean up created objects

EXECUTE (
'USE TempDB
IF OBJECT_ID(''dbo.Table1'') IS NOT NULL
DROP TABLE dbo.Table1'
) AT [linkedserver];
EXECUTE sp_dropserver 'linkedserver'

Additional Examples

For additional examples that show using INSERT...SELECT * FROM OPENROWSET(BULK...), see the following topics:

See Also