SQL.7.0 import using MS ODBC Driver for DB/2?

I am running SQL Server 7.0 and currently testing Host Integration Server to copy tables from a database on an AS/400. I have configured the Microsoft ODBC Driver for DB/2 as my Data Source.

When I try to import a table, the import begins but I receive the following error msg " Error at Destination for Row number xxxx Arithmetic overflow error converting numeric to data type numeric".

I can download the same table via SNA Server using the STAR SQL ODBC driver on another without error but not between the AS400 and the server. I cannot figure out what could be wrong. Any help would be appreciated.

IMport the table to a SQL Server table with varchar columns. Then import to the destination table using CONVERT function

I seem to be able to import tables that are all CHAR. But most of my tables have decimal fields. My imports work without conversion when I use SNA 3.0 what's up with Host Interagration Server?

if you want to avoid importing in to sql server temp table and then do conversion to import to destination table, you can import with conversion row by row using Activex.


Host integration
-----------------
http://www.cs.ntu.edu.au/homepages/r...alks/aiden.doc

http://www.dnjonline.com/articles/to...25_reviews.asp
http://asia.cnet.com/itmanager/netad...9117027,00.htm

http://msdn.microsoft.com/library/de..._db2_oledb.asp

Thanks for the feedback. I'll read up on the links you sent. Could this problem be linked to the ODBC driver? Because I can copy the same files without error using an OBDC driver called STAR SQL that comes with SNA Sever 3.0? I am moving from SNA Server to Host Integration Server, which does not use STAR SQL.

Any ideas?

What is the precision and scale of numeric data column in source and target.

Make sure SQL Server table you are importing into can handle the pricsion of the table in source.

Another option is, in DTS, use Query to get the source data and use CONVERT function to change the column data to varchar. Then in DTS Transformation properties for the column, convert the data back to numeric.

Hi skhanal,

The reason, I'm against going the conversion route at least right now, is that I can download these tables from the AS400 using SNA Server 3.0 with it's STAR SQL odbc driver. I am in the process of replacing upgrading SNA 3.0 with Host Integration Sever which does not use STAR SQL, but MS ODBC for DB/2.

I have another Server configured with SNA and I can download and copy tables without error. I can also copy tables between SQL Servers or to the Server running HIS without error. But the HIS server using the MS ODBC for DB/2 driver gives me the error when I use it to download from the AS/400. It downloads CHAR tables fine. But the CHAR/decimal tables fails midpoint in the download. I'm wondering if the problem lies with the ODBC driver?

It's possible.

If I understand you correctly (pardon my ignorance about SNA and DB/2)

You can import the table into sql server using STAR SQL odbc driver but can't do the same using ODBC for db/2 driver.

Have you tried OLE DB for ODBC driver?

Hi Skhanal,
You are correct. And no I have not tried OLE DB for ODBC driver? I will give it a shot.

Thanks

Skhanal,

The OLE DB for ODBC driver option seems to work. I just have to do a bit more testing. Thanks a mil.

Katy

Hi Again,
I tried OLE for DB/2 and it only copies when I try to run others. I'm thinking that both these ODBC drivers are not compatible with my AS/400 table design.
I have been looking at using MIcrosft OLE DB provider for AS/400 and VSAM. Any ideas?

"I tried OLE for DB/2 and it only copies when I try to run others"

I mean I can copy some tables without error but not others. It seems that if the table name on the AS400 contains any characters eg. MS# this row will cause the job to fail.