Hi guys....
I am upgrading from SQL Server 2000 to 2005 x64.
I have made a connection to an oracle database. In one of the Oracle tables, there is a field named "numID", which is Number(8).
So, when I run this query:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID > 100 and numID < 1000');
I get this message:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
(I do see some rows returned before the error.)
However, this query works:
select numID
from Openquery(myConnection, 'select numID from OracleTable where numID = 6991');
This query also works:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');
Does anyone know how to get this to work without doing multiple conversions?
Thanks,
Forch
not familiar with oracle syntax. . .Is there an IsNumeric function and if so, what happens if you execute something along the lines of
select numID
from Openquery(myConnection, 'select numID from OracleTable where not IsNumeric(numID) = 1');
do you get any hits?
how about with
select numID
from Openquery(myConnection, 'select numID from OracleTable where IsNumeric(numID) = 1 and numID between 100 and 1000');
|||
I have the very same issue on my x64.
My server is a Windows 2003 Advanced Server with SQL 2005 SP1 x64. The ODBC driver is ORACLIENT10g version 10.02.00.01. The statement is something like. The source Oracle database is version 9i (9.20.50)
select TERM_CD,TERM_DAYS,TERM_UNIT_CD,TERM_UNITS,BEGIN_DT,END_DT,NO_OF_ISSUES,DESCRIPTION,DMSI_FLG,CS_FLG,FULFILLMENT_FLG,PAID_TERM_FLG,UPGRADE_FLG,UPGRADED_TERM_CD,CREATED_DT,UPDATED_DT,PRODUCT_CD,PRODUCT_STATUS_CD,SUB_CATEGORY_CD,SUB_CATEGORY_DESC,PRODUCT_CATEGORY_CD,PRODUCT_CATEGORY_DESC,PRODUCT_BRAND_CD,PRODUCT_BRAND_DESC,PRINT_COPY_FLG,ELECTRONIC_COPY_FLG,BUNDLED_COPY_FLG,DMSI_ADMIN_FLG,FREQ_OF_DELIVERY,SMARTLINK_FLG,SL_ISSUES
from openquery (pibd,'select * from term_ref where term_cd = ''AE''')
The query runs fine with out the TERM_DAYS field. This is a numeric field.
One of the fields in the query (TERM_DAYS) is a numeric field. I checked the data on Oracle table and the data is good. When I eliminate this specific field from my query string, I get results. There are several other numeric fields in the query, but the error only occurs when I include this field
I suspect that the issue might be the ODBC driver I have installed. However, the version above was at the time the only version that worked with SQL2005.
I like to know if anyone out there has encountered a similar issue and what was they did to resolve the problem.
Thanks
|||
From this Oracle forum post, it looks like MS have stated that it is a provider problem with Oracle 10.x drivers. Not sure if it's specifically 64-bit drivers, though the post refers specifically to these, and I have experienced the same issue on an x64 platform.
One suggestion in the forum is to create a view to round the numerics.
http://forums.oracle.com/forums/thread.jspa?threadID=337842&tstart=0
|||
Above suggestion is not working..
|||
I too came across this error with SQL Server 2005 x64 & 64bit Oracle client... To overcome it I did the following:
I looked at my data in Oracle using SQLPlus (& SQL Developer). I verified that the precision of the values for this column didn't exceed 6 decimal places. Then in my Oracle database I created a view and I rounded this number to a precision of 6-- round(columnname,6). Now when I do a select * from openquery(datasource, 'select * from viewname') it is returning my full data set...
Hope this helps someone, and saves someone some time...
Rob
|||
I agree with Llewellin (Rob), the root cause for this issue is numeric precision or conflicitng numeric definition..
as Forch said the best solution for this to use the following query and control your numeric data precision & definition on SQL Server..
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select To_Char(numID) as numID from OracleTable');
|||
It's amazing to me that this continues to be an ongoing problem, but I am also having problems with numeric data types when using Windows 2003 x64 with x64 Oracle ODBC driver (using DataReader through Visual Studio 2005 Integration services). They get translated in as 0's when I begin processing the data in the VS 2005/SQL Server 2005 environment. The solution I have come up -- though I still find it very inconvenient -- is to put to_number around each numeric column....e.g.
Select my_num_field1,
my_char_field2,
my_num_field3
from my_table;
Where my_num_field1 is NUMBER(8,0) and my_num_field3 is NUMBER(11,4).
Change it to:
Select to_number(my_num_field1) my_num_field1,
my_char_field2,
to_number(my_num_field3) my_num_field3
from my_table;
Or run the job using 32 bit integration services and 32 bit Oracle ODBC driver.
I'm using x64 bit Oracle InstantClient version 10.2.0.3.
Are there other's seeing this same problem? Any other suggestions or workarounds?
|||
I too have the error. i have only just started to investigate.
I had a select working fine on 32bit which seems to have broken without any change
|||
I just ran into this problem using the 64bit client also. I solved in in a similar fashion by doing the following:
select Convert(Int, numID) as numID
from Openquery(myConnection, 'select CAST(numID AS NUMBER) as numID from OracleTable');
To shed a little more light on the subject, the problem appears to be isolated to only INTEGER or NUMBER(n) values that end with a 0.
I found that I get this error whenever I select rows where the column contains a number like 10 or 1090. But if no rows contain a value that ends with a 0, the query succeeds.
