OPENROWSET (T-SQL)

Includes all connection information necessary 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 using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is 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 OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Syntax

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

Arguments

'provider_name'

Is a character string that represents the friendly name of the OLE DB provider as specified in the registry. provider_name has no default value.

'datasource'

Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the provider’s IDBProperties interface 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 to locate the database(s).

'user_id'

Is a string constant that is the username that is passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider.

'password'

Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider.

'provider_string'

Is 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 needed to initialize the provider.

catalog

Is the name of the catalog or database in which the specified object resides.

schema

Is the name of the schema or object owner for the specified object.

object

Is the object name that uniquely identifies the object to manipulate.

'query'

Is a string constant that is sent to and executed by the provider. Microsoft® SQL Server™ does not 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 do not expose 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 OLE DB Programmer’s Reference.

Remarks

Catalog and schema names are required if the OLE DB provider supports multiple catalogs and schemas in the specified data source. Values for catalog and schema can be omitted if the OLE DB provider does not 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.

Permissions

OPENROWSET permissions are determined by the permissions of the username being passed to the OLE DB provider.

Examples

A. Use OPENROWSET with a SELECT and the Microsoft OLE DB Provider for SQL Server

This example uses the Microsoft OLE DB Provider for SQL Server to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized from the datasource, user_id, and password, and a SELECT is used to define the row set returned.

USE pubs

GO

SELECT a.*

FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',

    'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a

GO

B. Use OPENROWSET with an object and the OLE DB Provider for ODBC

This example uses the OLE DB Provider for ODBC and the SQL Server ODBC driver to access the authors table in the pubs database on a remote server named seattle1. The provider is initialized with a provider_string specified in the ODBC syntax used by the ODBC provider, and the catalog.schema.object syntax is used to define the row set returned.

USE pubs

GO

SELECT a.*

FROM OPENROWSET('MSDASQL',

    'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',

    pubs.dbo.authors) AS a

ORDER BY a.au_lname, a.au_fname

GO

C. Use the Microsoft OLE DB Provider for Jet

This example accesses the orders table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.

Note This example assumes that Access is installed.

USE pubs

GO

SELECT a.*

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
    AS a

GO

D. Use OPENROWSET and another table in an INNER JOIN

This example selects all data from the customers table from the local SQL Server Northwind database and from the orders table from the Access Northwind database stored on the same computer.

Note This example assumes that Access is installed.

USE pubs

GO

SELECT c.*, o.*

FROM Northwind.dbo.Customers AS c INNER JOIN
    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
    AS o
    ON c.CustomerID = o.CustomerID

GO

See Also