OLE DB Providers Tested with SQL Server
Microsoft® SQL Server™ 2000 distributed queries have been tested with several OLE DB providers. Some of the tested OLE DB providers are installed with SQL Server 2000:
- Microsoft OLE DB Provider for SQL Server
- Microsoft OLE DB Provider for ODBC
- Microsoft OLE DB Provider for Jet
- Microsoft OLE DB Provider for DTS Packages
- Microsoft OLE DB Provider for Oracle
The other tested providers are available through Microsoft Windows® 2000 (Microsoft OLE DB Provider for Microsoft Directory Services and the Microsoft OLE DB Provider for Microsoft Indexing Service), and the Microsoft Host Integration Server (Microsoft OLE DB Provider for DB2).
This table shows the OLE DB providers that have been tested with SQL Server distributed queries. All of these 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 pass-through queries | Use in INSERT, UPDATE, or DELETE | Use in distributed transactions |
|---|---|---|---|---|---|
| SQL Server 6.5 or later | Microsoft OLE DB Provider for SQL Server | Yes | Yes | Yes | Yes |
| ODBC Data Sources | Microsoft OLE DB Provider for ODBC | Yes* | Yes | Yes* | Yes* |
| Microsoft Access (Jet) databases | Microsoft OLE DB Provider for Jet version 4.00 | Yes | Yes | Yes (No if the database was created with Microsoft Jet version 4.0 or earlier) | No |
| Microsoft Excel spreadsheets | Microsoft OLE DB Provider for Jet version 4.00 | Yes | Yes | Yes | No |
| Data Transformation Service Package Data Source Object | Microsoft OLE DB Provider for DTS Packages | Yes | Yes | No | No |
| Oracle databases | Microsoft OLE DB Provider for Oracle version 2.6 | Yes | Yes | Yes | Yes |
| Microsoft Windows® 2000 Directory | Microsoft OLE DB Provider for Microsoft Directory Services | No | Yes | No | No |
| 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 | Yes | No | No |
| IBM DB2 databases | Microsoft OLE DB Provider for DB2 | Yes | Yes | Yes | 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.
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.
If a provider does not support being used in a four-part name, it can be referenced in an OPENQUERY or OPENROWSET function using a pass-through query.