1 2 Previous Next 21 Replies Latest reply: May 19, 2008 4:11 AM by 464408 RSS

    OraOledb, Linked Servers and SQL Server 2005 issues

    464960
      Some issues I've come across when using SQL Server 2005 (and SQL Server Express 2005), the OraOLEDB provider (10.2.0.1) and a linked Oracle database (8.1.7.4.0 64-bit)

      1) You must set the OraOledb.Oracle\AllowInProcess value to 1 to allow the OraOledb provider to run in SQL Server's process. Without doing this I receive an 'unspecified error' from the OLE DB provider when attempting to run a query

      2) When running a ' select * ' query across a linked server using the provider, I receive the following error: Msg 9803, Level 16, State 1, Line 1
      Invalid data for type "numeric". I can, however, select all of the columns by name and the query completes (no error). Sometimes the 'select *' query returns a few rows before the error, sometimes it doesn't. The Microsoft Provider for Oracle does not have this problem
        • 1. Re: OraOledb, Linked Servers and SQL Server 2005 issues
          486834
          I can confirm this EXACT same problem using the 64bit version of SQL Server 2005 Developer with the 64-bit Oracle Client v10.2.0.1 going againt a linked Oracle Database (Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production). I can't check the Microsoft Oracle Driver as it doesn't yet exist for 64-bit. :(
          • 2. Re: OraOledb, Linked Servers and SQL Server 2005 issues
            492953
            I would like to confirm the same issue (Msg 9803, Level 16, State 1, Line 1
            Invalid data for type "numeric".) on Windows Server 2003 SP1 R2 AMD64 w/SQL Server 2005 Ent. AMD64 linking Oracle 8i via Oracle.OLEdb 10.2.0.1 64-bit driver (from 64-bit client tools)...; any workarounds?
            • 3. Re: OraOledb, Linked Servers and SQL Server 2005 issues
              494529
              Same for me. I'm assuming this is an MS issue since everything else seems to be able to use it fine. I've got a thread "Errors in oracle linked server in x64" in the sql2005 forums if anyone else wants to join in.
              • 4. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                494529
                Finally got an answer on this. There is a problem with the oraoledb driver on converting numeric datatypes where it ignores all trailing zeros. Oracle is supposedly aware of this though so hopefully a fix will be available soon.
                • 5. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                  504051
                  Hi guys....

                  where can I find the 64-bit edition of the driver?
                  • 6. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                    511597
                    Finally got an answer on this. There is a problem
                    with the oraoledb driver on converting numeric
                    datatypes where it ignores all trailing zeros.
                    Oracle is supposedly aware of this though so
                    hopefully a fix will be available soon.
                    Do you know when a fix will be out (or is it out already)?

                    /david
                    • 7. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                      512350
                      I'm getting the error even if I explicitly name the column:

                      select myColumn from OPENQUERY(ORACLE,'Select myColumn from table')

                      but I only get the error if it is a negative number?!

                      any ideas?
                      • 8. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                        491414
                        I have run into the wonderful 'Invalid data for type "numeric"' with an Oracle Linked server in SQL 2005. I opened a case with MS and they said this was due to a bug in 10.2.0.x that was not in 10.1.0.x client. However, I cannot confirm this seeing that Oracle doesn't have any 10.1.0.x client for the 64bit and AMD procs - Arrg! I am going to spend this afternoon testing the 32bit version and see if that repro's the problem. In any case, though, I have a case open with Oracle but it is slow going since they want the table create pl\sql and I do not have it. If they find a solution, I'll post back...

                        Btw, has there been any word on the supposed fix for this?
                        • 9. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                          491414
                          No go with Client 10.1 - I was able to reproduce the problem...
                          • 10. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                            514311
                            I received the numeric error even though specifying the entire column names.

                            Solution that worked for me: Use the Oracle Function ROUND() on the column, this changed the number so mssql would accept it. (Maybe other numeric functions will have same effect)

                            I hope this helps someone, as I struggled some time with this.

                            Regards

                            Peter
                            • 11. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                              523556
                              Does anyone have any updates for this problem? I am using Sql Server 2005 Express on a WinXP, Oracle 10.2 Client against an Oracle 8.1.7 database and I am also getting this error on a numeric field.

                              Message was edited by:
                              user520553
                              • 12. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                                526702
                                Okay... I've got a Win2K3 Std Ed server (x64) running 64-bit SQL Server 2005 Enterprise Edition. I've installed the Oracle 10g 10.2.1 full client and admin tools, added two named services via the NetConfig assistant, and successfully set up (and tested) a connection via the ODBC Administrator to an Oracle database.

                                Now... when I try to create a new connection manager in SQL Server 2005 Integration Services, the OLEDB provider for Oracle can't be found, and when I try to manually add an underlying OLEDB connection to the database, SQL Server reports:

                                Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

                                Does anyone know what I need to do to see my ODBC Server data connections in SQL Server 2005 (64 bit)? I don't have this issue on my 32-bit SQL Server 2005 servers.
                                • 13. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                                  536110
                                  I'm using SQL Server 2005 64 Bit and having a problem when I try to connect to Oracle 10G 64 Bit (installed on the same server). I'm using "Oracle Provider for OLE DB" to connect but from what I've read in Books Online that the provider doesn't support for Oracle 64 Bit. Can you please tell me how to fix it ? Maybe I should install another provider such as "Microsoft OLE DB Provider for Oracle" ? If so, where can I find or download that provider for SQL 2005 64 Bit. Thanks.

                                  Regards,
                                  Albert
                                  • 14. Re: OraOledb, Linked Servers and SQL Server 2005 issues
                                    543115
                                    One work around to this problem is to use OPENQUERY and pass in the query with the Oracle's cast from numeric data type to a varchar. You just need to cast the varchar columns back to Numeric when the recordset comes to SQL Server. This way, you bypass the implict conversion of the numeric data type on the driver level.

                                    SELECT *
                                    FROM OPENQUERY
                                    (
                                         ORACLE_LinkServer,
                                         'SELECT
                                         to_char(Col_A) as COLA,
                                         to_char(Col_B) as COLB
                                         FROM Table_A'
                                    )

                                    Hope this helps.

                                    Eric K.
                                    1 2 Previous Next