Skip to content

linked-servers-openquery-openrowset-exec-at.md

Latest commit

 

History

History
184 lines (133 loc) · 8.84 KB

linked-servers-openquery-openrowset-exec-at.md

File metadata and controls

184 lines (133 loc) · 8.84 KB
description Compares OPENROWSET, OPENQUERY, EXECUTE AT as options for remote execution
title Query remote servers (Database Engine)
ms.date 12/02/2021
ms.prod sql
ms.technology
ms.prod_service database-engine
ms.reviewer
ms.topic conceptual
helpviewer_keywords
OLE DB, linked servers
OLE DB provider, linked servers
server management [SQL Server], linked servers
linked servers [SQL Server]
distributed queries [SQL Server], linked servers
servers [SQL Server], linked
remote servers [SQL Server], linked servers
linked servers [SQL Server], about linked servers
author WilliamDAssafMSFT
ms.author wiassaf
ms.custom seo-dt-2019

Compare query remote execution options

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('SQLNCLI', [linkedserver],
     'SELECT * FROM AdventureWorksLT.SalesLT.Customer') AS a;

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