Using Pass-Through Queries as Tables
Microsoft® SQL Server™ 2000 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.
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'' ')
The ways to generate a rowset from an OLE DB provider are:
- 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.
When a provider supports the Command object, these Transact-SQL functions can be used to send it commands (called pass-through queries):
- OPENQUERY sends a command string to an OLE DB data source using a linked server name.
- OPENROWSET and OPENDATASOURCE support sending a command string to an OLE DB data source. The resulting rowset can be referenced using an ad hoc 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 SQL language. Other types of providers, such as those exposing the data in an e-mail file or network directory, generally support a different language.