This content has been marked as final. Show 21 replies
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 22.214.171.124.0 - 64bit Production). I can't check the Microsoft Oracle Driver as it doesn't yet exist for 64-bit. :(
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?
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.
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.
where can I find the 64-bit edition of the driver?
Finally got an answer on this. There is a problemDo you know when a fix will be out (or is it out already)?
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.
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?!
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?
No go with Client 10.1 - I was able to reproduce the problem...
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.
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:
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.
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.
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.
to_char(Col_A) as COLA,
to_char(Col_B) as COLB
Hope this helps.