Using Pass-Through Queries As Tables
SQL Server 2005 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 were a regular table reference.
The following are 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 its mandatory interfaces.
When a provider supports the Command object, the following Transact-SQL functions can be used to send the provider commands, called pass-through queries:
- OPENQUERY sends a command string to an OLE DB data source by 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 by using an ad hoc name.
OPENROWSET and OPENQUERY support pass-through queries in which the query string contains a stored procedure call only when the query has one of the following characteristics:
- Executes a single, static SELECT statement.
- Does not use any dynamic EXECUTE statements.
- Does not perform any data modification operations.
The OLE DB specification does not define one command language that must be used by all OLE DB providers. OLE DB providers can 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.
See Also
Concepts
Distributed Queries
Accessing External Data
Other Resources
FROM (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
OPENROWSET (Transact-SQL)