OLE DB Providers Tested with SQL Server
SQL Server 2005 distributed queries have been tested with several OLE DB providers. The following tested OLE DB providers are installed with SQL Server 2005:
- Microsoft SQL Native Client OLE DB Provider. This is the OLE DB provider for SQL Server 2005.
- Microsoft OLE DB Provider for ODBC
- Microsoft OLE DB Provider for Jet
- Microsoft OLE DB Provider for Oracle
The other tested providers are:
- Microsoft OLE DB Provider for Microsoft Directory Services
- Microsoft OLE DB Provider for Microsoft Indexing Service
- Microsoft OLE DB Provider for DB2
- Microsoft Exchange Server (Microsoft OLE DB Provider for Exchange).
- Oracle Provider for OLE DB is available from Oracle Corporation.
The following table shows the OLE DB providers that have been tested with SQL Server distributed queries. All 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 and commands | Use in INSERT, UPDATE, or DELETE | Use in distributed transactions |
|---|---|---|---|---|---|
SQL Server 7.0 or later |
Microsoft SQL Native Client OLE DB Provider |
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 by using Microsoft Jet version 4.0 or earlier) |
No |
Microsoft Excel spreadsheets |
Microsoft OLE DB Provider for Jet version 4.00 |
Yes |
Yes |
Yes |
No |
Oracle databases |
Microsoft OLE DB Provider for Oracle |
Yes |
Yes |
Yes |
Yes |
Oracle databases** |
Oracle Provider for OLE DB (8.1.7.3.0) for Windows 2000/NT |
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 |
Yes |
Microsoft Exchange Server |
Microsoft OLE DB Provider for Exchange Server |
No |
Yes |
No |
No |
* The capabilities of the Microsoft OLE DB Provider for ODBC depend on the ODBC driver that is being used. The provider may not support all these capabilities with some ODBC drivers.
** Support for the Oracle Provider for OLE DB is as follows:
Oracle 32-bit |
Out-of-proc only |
Oracle 64-bit |
None |
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, the provider can be referenced in an OPENQUERY or OPENROWSET function by using a pass-through query.