Results 1 to 12 of 12

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

  1. #1
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    IMport the table to a SQL Server table with varchar columns. Then import to the destination table using CONVERT function

  3. #3
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52

    Doesn't work

    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?

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  5. #5
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52

    response

    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?

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  7. #7
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    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?

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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?

  9. #9
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Hi Skhanal,
    You are correct. And no I have not tried OLE DB for ODBC driver? I will give it a shot.

    Thanks

  10. #10
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Skhanal,

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

    Katy

  11. #11
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    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?

  12. #12
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    "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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •