1 2 Previous Next 21 Replies Latest reply: May 19, 2008 4:11 AM by 464408 Go to original post RSS
      • 15. Re: OraOledb, Linked Servers and SQL Server 2005 issues
        543209
        This is not new guys. It's sad that MIcrosoft and Oracle are well aware of this problem but neither wants to fix it. MS does not have a 64-bit version of MSDAORA and Oracle's OraOLEDB is not friendly with implicit data conversion.

        These two companies are playing games at our expense.
        • 16. Re: OraOledb, Linked Servers and SQL Server 2005 issues
          408120
          Hi

          We are facing same problem. What is the solution? Let me know
          • 17. Re: OraOledb, Linked Servers and SQL Server 2005 issues
            256947
            We had the same problem, so I checked Metalink and found:

            You are running into the following bug:

            Bug 5011258 SQL SERVER 2005 LINKED SERVER GENERATES INVALID DATA FOR TYPE NUMERIC

            Solution
            Upgrade the Oracle Provider for OLE DB (32-bit or 64-bit) to version 10.2.0.2.20 or higher.

            The 10.2.0.2.20 32-bit version is available from metalink.oracle.com as Patch 5473334.

            The 10.2.0.2.21 64-bit version for AMD/EM64T and Itanium is available from technet.oracle.com at the following URL:

            http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

            This worked for us.
            • 18. Re: OraOledb, Linked Servers and SQL Server 2005 issues
              608774
              We had the problem on a couple win clusters but it worked on a standalone; finally figured out that 1. odac must be in a separate oracle home. 2. set the system environment variable to point to the odac home. The msdaora adapter also worked fine. the win servers are all 32-bit. We'll be uninstalling the clients on all servers and only installing the latest version of odac mentioned in the last post: The 10.2.0.2.20 32-bit version is available from metalink.oracle.com as Patch 5473334.

              Now if I could only get oracle stored procs to execute...I'm getting Msg 7357, Level 16, State 2, Line 1
              Cannot process the object "exec SS2005USER.O_REFCUR.GETCLASSAD('038308410001',to_date('2008-01-21','yyyy-mm-dd')". The OLE DB provider "OraOLEDB.Oracle" for linked server "MSSP" indicates that either the object has no columns or the current user does not have permissions on that object.
              • 19. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                630517
                user511308, Peter Obel, mentioned that he used the ROUND() function to get around the invalid data type "numeric" error. Could someone provide an example of how this works when using openquery also? Or, has anyone found another way to massage the data to work with this driver?

                Thanks!
                • 20. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                  514311
                  Dear user627514

                  I used:

                  SELECT ID, NAME
                  FROM OPENQUERY(remotedb, 'SELECT round(PERSON_ID,0) as PERSON_ID, NAME FROM infoweb.people') AS derivedtbl_1

                  I hope this helps, I know it's not optimal, but it does work.

                  Peter
                  • 21. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                    464408
                    I solved this problem via Metalink DocID 369814.1
                    In essence the solution is to install an Oracle OLEDB driver with minimum version 10.2.0.2.20.
                    I installed an Oracle client 10.2.0.3 and then applied the applicable patch (described in the Metalink DocID 369814.1)

                    Kind regards,

                    Paul Wever
                    1 2 Previous Next