Using Pass-through Queries as Tables

There are two ways to generate a rowset from an OLE DB provider:

• Reference an object in the data source that the provider can expose as a tabular rowset. All providers support this capability.
• Send the provider a command that the provider can process and expose the results of the command as a rowset. This capability requires that the provider support the OLE DB Command object and all of its mandatory interfaces, which is not supported by all providers.

When a provider supports the Command object, these Transact-SQL functions can be used to send it commands (called pass-through queries):

• OPENQUERY

  OPENQUERY is used to send a command string to an OLE DB data source using a linked server name.

• OPENROWSET

  OPENROWSET supports sending a command string to an OLE DB data source. The resulting rowset can be referenced using an ad hoc connector name.

The OLE DB specification does not define a single command language to be used by all OLE DB providers. OLE DB providers are allowed to support any command language that is related to the data they expose. OLE DB providers that expose the data in relational databases generally support the DBGUID-SQL command language, which is based on SQL-92 and ODBC SQL. Other types of providers, such as those exposing the data in an e-mail file or network directory, are likely to support a different language.

Microsoft® SQL Server™ sends pass-through queries as uninterpreted query strings to an OLE DB data source. The query must be in a syntax the OLE DB data source will accept. A Transact-SQL statement uses the results from a pass-through query as though it is a regular table reference. Pass-through queries are read-only. Pass-through queries cannot be used as the table being modified in an UPDATE, DELETE or INSERT statement.

This example uses a pass-through query to retrieve a result set from a Microsoft Access version of the Northwind sample database.

SELECT *

FROM OpenRowset('Microsoft.Jet.OLEDB.4.0',

    'c:\northwind.mdb';'admin'; '',

    'SELECT CustomerID, CompanyName

         FROM Customers

         WHERE Region = ''WA'' ')

See Also