This discussion is archived
4 Replies Latest reply: Jan 29, 2008 9:16 AM by 620144 RSS

OLE DB provider 'OraOLEDB.Oracle' reported an error The provider did not gi

620144 Newbie
Currently Being Moderated
Hi, I've setup oracle client tools on a windows server that runs sql server 7
tnsping works as well as the client tool test feature on my sid.

However, I can't seem to use it from SQL Server. I've installed and uninstalled the client tools many times, I'm beginning to think perhaps I installed the wrong package. Weird thing is there is no Listener listed in Windows Services, yet the listener I setup is working.

I enabled the allow in process property of both the MSADAORA and Oracle OLE drivers from SQL Server, help!!

SELECT * FROM OPENQUERY(ORACLE_MIRROR, 'select count(1) from dual')

RESULTS IN:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:   ].

THIS IS AFTER I HAVE INSTALLED THE ORACLE 10.2 CLIENT TOOLS AND SETUP A LISTENER AND TESTED SIDS OK.

UTILIZING THE ORACLE OLE DRIVER IN THE LINKED SERVER I GET THIS ERROR:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IDBInitialize::Initialize returned 0x80004005:  The provider did not give any information about the e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 1. Re: OLE DB provider 'OraOLEDB.Oracle' reported an error The provider did not gi
    kgronau Guru
    Currently Being Moderated
    The MS OLEDB for Oracle was built some time ago and requires an older SQL*Net version:
    OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
    => see Oracle Version V7.3.3 ... but you have installed V10; so the OLEDB driver from MS tries to locate old SQL*Net client library files which are not available in your installation anymore.

    So best would be to use the Oracle OLEDB driver coming with the SQL*Net client you have installed.

    As you mentioned this causes a different error:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'OraOLEDB.Oracle' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the e


    This error is related to the OLEDB process running by default in "out-of-process" mode which doesn't work with Oracle OLEDB driver. By setting a key in the registry under SQL Server this can be changed to run in-process (the default) the provider will work:

         HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ORAOLEDB.ORACLE

    Change the value of the key "AllowInProcess" under the "OraOLEDB.Oracle" folder
    to a hexadeciaml value of 1.

    If the key does not exist create it as a new DWORD value.

    See also:
    Article-ID:         Note 260775.1
    Title:              ORA-12560 Attempting to Query the Oracle Database from
                        Microsoft SQL Server's SQL Query Analyzer Using the Oracle
                        Provider for OLE DB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 2. Re: OLE DB provider 'OraOLEDB.Oracle' reported an error The provider did not gi
    620144 Newbie
    Currently Being Moderated
    Thanks for your help, it is much appreciated. The AllowInProcess is already setup and I have the same problem with the Oracle OLE driver (OraOLEDB.Oracle) which has the AllowInProcess option setup too. When I go to view the tables in the catalog of the linked server, I get an hourglass eternally.
  • 3. Re: OLE DB provider 'OraOLEDB.Oracle' reported an error The provider did not gi
    620144 Newbie
    Currently Being Moderated
    The Net Manger test works correctly. But there is no Oracle Listener service running in windows services which seems strange. Is this listener service required to interface with MS SQL Server? I only have the OracleMTSRecovery service running/listed. If the listener service is required how do I install it? I thought the Oracle client would install it. Thanks.
  • 4. Re: OLE DB provider 'OraOLEDB.Oracle' reported an error The provider did not gi
    620144 Newbie
    Currently Being Moderated
    FOUND SOLUTION: After installing the Oracle client you must Manually REBOOT the SERVER:

    Oracle linked servers were not working. The specific errors are listed below. The resolution was to ensure Windows had the latest MDAC installed and that the Oracle Client was the same as that used on the production SQL Server. After installing the Oracle client ( found in the dfpfs4\apps\Oracle\Oracle10g folder) and configuring the Listener and SIDs – the Oracle Client reported the connections as working – however the SQL Linked Servers were not working. Rebooting the server resolved this issue, as the Oracle install does not register the dlls for consumption in SQL Server until the server is rebooted.