INFO: Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Both the Microsoft ODBC for Oracle driver (Msorcl32.dll) and the Microsoft OLE DB Provider for Oracle (Msdaora.dll) use the SQL*Net Client (or Net8 client for Oracle 8x) and the Oracle Call Interface (OCI) library, and also other Oracle client components, to connect to Oracle databases and retrieve data. The Oracle client components are important, and must be configured correctly to successfully connect to Oracle databases using both the driver and the provider.

To debug the Oracle connectivity issues with either the Microsoft ODBC driver for Oracle or the Microsoft OLE DB Provider for Oracle, follow these steps:

1. Use the Oracle SQL Plus utility (a command line-based query utility) to verify that you can connect to Oracle and retrieve data.
   
   NOTE: If you cannot connect to Oracle and retrieve data, you either have a bad install or configuration of the Oracle Client Components or you have not correctly created a Transparent Network Substrate (TNS) service alias for the Oracle server when you used the SQL*Net Easy Configuration or Oracle Net8 Easy Configuration utility. Contact your Oracle database administrator (DBA) to verify that the Oracle components that you must have are correctly installed and configured.
2. Verify the version of the Oracle client (SQL*Net version) that is installed on the computer. On a computer running Windows NT, use the NT.RGS file located in the Oracle_Root\Orainst folder to verify the version. On a non-Windows NT client, use the Win95.rgs file in the Oracle_Root\Orainst folder to verify the version. Both the Microsoft ODBC driver for Oracle and the Microsoft OLE DB Provider for Oracle require the installation of SQL*Net version 2.3 or later on the client computer.
   
   The connectivity from SQL Plus (the Oracle client query tool) may appear to function, but you must restart your computer for the ODBC/OLE DB connectivity to function correctly.
   
   NOTE: When you use Oracle 8i, the .rgs file is empty.
3. If the Oracle client is installed, and you receive an error that indicates that Oracle Client Components 7.3 or later must be installed on the computer, then verify that the environmental variable PATH on the client computer contains the folder in which the Oracle client was installed such as, Oracle_Root\Bin. If you cannot find this folder, then add the folder to the PATH variable to resolve the error.
4. Verify that the Ociw32.dll file is in the Oracle_Root\bin folder. This .dll file cannot exist at any other location on the client computer. Make sure that the Oracle Client Component DLLs (for example, the Core40.dll file and the Ora*.dll file) do not exist outside the Oracle_Root folder or subfolders.
5. Verify that a single Oracle client version is installed on the computer. Multiple versions of SQL*Net cannot exist on the same client computer with interferes and with critical operations (for example, TNS and alias lookups).
6. Microsoft recommends that you have a local install of the Oracle client and not do this by mapping a remote Oracle client on your computer and then include it in the path of the system to connect to Oracle through ODBC/OLE DB. But the provider and driver are tested with a locally installed Oracle client and not on a network share.
7. Verify that Oracle and Oracle Web server are not installed on the same computer. If this is the case, contact Oracle for a Hot Fix on product upgrade.