Microsoft® SQL Server™ distributed queries have been tested with several OLE DB providers. Although Microsoft supports only distributed queries that reference the providers tested by Microsoft, distributed queries should work with any OLE DB provider that meets the requirements documented in the OLE DB Provider Reference for Distributed Queries.
This table shows the OLE DB providers that have been tested with SQL Server distributed queries. All of the providers support being referenced in a SELECT statement by specifying a pass-through query in the OPENQUERY and OPENROWSET functions.
Data source |
Provider name |
Use in four- part names |
Use in INSERT, UPDATE, or DELETE |
Use in Distributed Transactions |
|---|---|---|---|---|
| Microsoft SQL Server 6.5 and 7.0 databases | Microsoft OLE DB Provider for SQL Server | Yes | Yes | Yes |
| Oracle databases | Microsoft OLE DB Provider for Oracle | Yes | Yes | Yes |
| Microsoft Access (Jet) databases | Microsoft OLE DB Provider for Jet version 4.0 | Yes | Yes (No if the database was created with a version of Jet earlier than 4.0.) | No |
| Excel spreadsheets | Microsoft OLE DB Provider for Jet version 4.0 | Yes | Yes | No |
| ODBC Data Sources | Microsoft OLE DB Provider for ODBC | Yes* | Yes* | Yes* |
| Local file system (through Indexing Services) | Microsoft OLE DB Provider for Microsoft Indexing Service (Requires Microsoft Windows NT® 4.0 Service Pack 4 or later) | No | No | No |
| Data Transformation Service Package Data Source Object | Microsoft OLE DB Provider for DTS Packages | Yes | No | No |
| * The capabilities of the Microsoft OLE DB Provider for ODBC depend on the ODBC driver being used. The provider may not support all these capabilities with some ODBC drivers. | ||||
If a provider does not support being used in a four-part name, it can be referenced only in an OPENQUERY function executing a pass-through query.
The following OLE DB providers are installed with SQL Server and can be used to support distributed queries: