4 Replies Latest reply: Jan 29, 2008 11:16 AM by 620144 RSS

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

    620144
      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-Oracle
          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
            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
              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
                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.