Edit

Share via

OPENROWSET (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions

OPENROWSET is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data. An OPENROWSET T-SQL command includes all connection information that is required to access remote data from an external data source.

The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the data provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

Tip

For more frequent references to external data sources, use linked servers instead. For more information, see Linked Servers (Database Engine).

OPENROWSET without the BULK operator is available on SQL Server only. Details and links to similar examples on other platforms:

Transact-SQL syntax conventions

Syntax

OPENROWSET syntax is used to query external data sources:

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

Arguments

'provider_name'

A character string that represents the friendly name (or PROGID) of the data provider as specified in the registry. provider_name has no default value. Provider name examples are MSOLEDBSQL, Microsoft.Jet.OLEDB.4.0, or MSDASQL.

'datasource'

A string constant that corresponds to a particular data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands for locating the database or databases.

Data source can be file path C:\SAMPLES\Northwind.mdb' for Microsoft.Jet.OLEDB.4.0 provider, or connection string Server=Seattle1;Trusted_Connection=yes; for MSOLEDBSQL provider.

'user_id'

A string constant that is the user name passed to the specified data provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id can't be a Microsoft Windows login name.

'password'

A string constant that is the user password to be passed to the data provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password can't be a Microsoft Windows password. For example:

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    '<user name>';
    '<password>',
    Customers
) AS a;

'provider_string'

A provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider.

For a list of keywords that the SQL Server Native Client OLE DB provider recognizes, see Initialization and Authorization Properties (Native Client OLE DB Provider). 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.

SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

[ catalog. ] [ schema. ] object

Remote table or view containing the data that OPENROWSET should read. It can be three-part-name object with the following components:

  • catalog (optional) - the name of the catalog or database in which the specified object resides.
  • schema (optional) - the name of the schema or object owner for the specified object.
  • object - the object name that uniquely identifies the object to work with.
SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'query'

A string constant sent to and executed by the provider. The local instance of SQL Server doesn't process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that don't make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces.

For more information, see SQL Server Native Client (OLE DB) Interfaces.

SELECT a.*
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

Remarks

OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options aren't set, the default behavior doesn't allow for ad hoc access.

When you access remote OLE DB data sources, the login identity of trusted connections isn't automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured.

Catalog and schema names are required if the data provider supports multiple catalogs and schemas in the specified data source. Values for catalog and schema can be omitted when the data provider doesn't support them. If the provider supports only schema names, a two-part name of the form schema.object must be specified. If the provider supports only catalog names, a three-part name of the form catalog.schema.object must be specified. For more information, see Transact-SQL syntax conventions.

Three-part names are required for pass-through queries that use the SQL Server Native Client OLE DB provider.

OPENROWSET doesn't accept variables for its arguments.

Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. In particular, filter or join conditions applied on the result of one of those calls has no effect on the results of the other.

Permissions

OPENROWSET permissions are determined by the permissions of the user name that is being passed to the data provider.

Examples

This section provides general examples to demonstrate how to use OPENROWSET.

Note

For examples that show using INSERT...SELECT * FROM OPENROWSET(BULK...), see OPENROWSET BULK (Transact-SQL).

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.

A. Use OPENROWSET with SELECT and the SQL Server Native Client OLE DB Provider

The following example uses the SQL Server Native Client OLE DB provider to access the HumanResources.Department table in the AdventureWorks2022 database on the remote server Seattle1. (Use MSOLEDBSQL for the modern Microsoft SQL Server OLE DB Data Provider that replaced SQLNCLI.) A SELECT statement is used to define the row set returned. The provider string contains the Server and Trusted_Connection keywords. These keywords are recognized by the SQL Server Native Client OLE DB provider.

SELECT a.*
FROM OPENROWSET(
    'MSOLEDBSQL', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT GroupName, Name, DepartmentID
         FROM AdventureWorks2022.HumanResources.Department
         ORDER BY GroupName, Name'
) AS a;

B. Use the Microsoft OLE DB Provider for Jet

The following example accesses the Customers table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.

Note

This example assumes that Microsoft Access is installed. To run this example, you must install the Northwind database.

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

C. Use OPENROWSET and another table in an INNER JOIN

The following example selects all data from the Customers table from the local instance of SQL Server Northwind database and from the Orders table from the Microsoft Access Northwind database stored on the same computer.

Note

This example assumes that Microsoft Access is installed. To run this example, you must install the Northwind database.

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

Related content