September 8, 2004 at 8:12 pm
I need to import data from an Oracle Rdb database into SQL Server 2000. Using DTS in Enterprise Manager I can import text data, but if I try to include numeric data the package fails with the error message: "[Oracle][ODBC][Rdb]%RDB-E-ARITH_EXCEPT, truncation of a numeric value at runtime -COSI-F-INPCONERR, input conversion error"
The supplier of the Oracle software thinks the problem is that the data type for the numeric values is bigint, but thought that installing the latest Oracle Rdb ODBC driver would fix it. I installed the Oracle Rdb driver version 3.01.00.01 but it still does not work, and we do not know what to try next.
With Crystal Reports I can access the numeric data with the same ODBC driver without problem, but I need it to work in DTS for a data warehouse project.
Can anyone tell me how to resolve this?
September 13, 2004 at 7:32 am
Mark,
Using any version of the Rdb odbc 2.x/3.x driver I was not able to import the Rdb BIGINT datatype using a DTS datapump. I got around this by creating a view in Rdb and CAST-ing all BIGINT columns to another type (one of the floating point types). Also note that can only do a max of 500 columns (and this is not well documented). I don't think you can do this CAST in the query box of the DTS sql task, so you'll have to use Rdb views.
Also, if you ever need to go the other way (have SQL Server update Rdb), I've done that a lot and can give you code/tips to save you some time.
Jeff
September 13, 2004 at 2:34 pm
Thanks Jeff.
I have been trying different queries to see what I can get from the rdb view, and have actually managed to get what I want. However it is puzzling and I don't understand the reasons for my original query not working.
For example, the following query selects data from the Account_Balances view for a single GL account, and it works fine.
SELECT COMPANY_CODE, ACCOUNT_CODE, FINANCIAL_YEAR, FINANCIAL_PERIOD, FINANCIAL_PERIOD_NAME, ACTUAL_PERIOD
FROM ACCOUNT_BALANCES
WHERE ACCOUNT_CODE = '9462'
ORDER BY ACCOUNT_CODE, FINANCIAL_PERIOD
I tried to restrict this to one financial year by adding
AND FINANCIAL_YEAR = 2004
to the where clause, but that failed with the input conversion error. However the puzzling thing is it works fine if I remove the FINANCIAL_PERIOD_NAME field, which is a string field. The ACTUAL_PERIOD field is the BIGINT data type, but I don't have a problem getting data from that when the period name field is removed.
I have no idea why the query doesn't work with the period name field included, but I can easily recreate that so am not too worried about it. I have reported these findings to the Oracle software supplier and asked him to look at his rdb views.
September 13, 2004 at 2:53 pm
That's really weird.
Also, if you need to use views for BIGINT CASTing be sure to keep the views as simple as possible. Performance can degrade unexpectedly over time. It's best to use a DTS data pump to copy individual Rdb tables over and then join them together on the SQL side.
I gave up on using the 3.x driver after testing several versions (for DTS usage) for Oracle Metalink support. The 2.x driver is not "thread safe" (so if you have several DTS data pump tasks running concurrently on a multi-CPU server you'll get many random errors and you'll have to set parallel task execution parameter from default 4 to 1), but it is much faster than the 3.x drivers. Strangely enough after going through all that, this configuration has been much more stable than OLE db for Oracle (nonRdb).
Another tip: set the fetch buffer size to a value around 100. That will speed up the DTS data pump tremendously.
Good luck,
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply