Results 1 to 14 of 14

Thread: Cannot import table with # sign in the column name

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

    Cannot import table with # sign in the column name

    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 OLE DB for DB2 Driver as my Data Source.

    I have a few tables on the 400 with # signs in the column name. I get the following error when I try to import SQl Error occured, Please consult the documentation for your specific DB2 version SQLSTATE 42702 SQLCODE:-206.

    Any help would be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    enclose the column name with [] and import.

  3. #3
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    I cannot change the column names on the AS/400 side. Is there another way

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I did not mean to change column name,

    use [] to enclose column name wherever you refer the column in your import utility (is it DTS?).

  5. #5
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Sorry I misunderstood, please calrify. Are you saying that I should Add the [] signs on the destination column name, because I cannot do that at the source. I am using DTS. DTS only allows you to change the column name on the destination side, not the source.

    The source column names will have to be edited on the AS/400 side, which I will not be able to do.
    Thanks

  6. #6
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Adding the [] sign to the destination name does not help.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    How are you selecting your source?

    Instead of importing table use query to get the table. And in your select use

    select [#c1] from table

    If [] does not work then you have to find what is equivalent of [] in db2, may be "#c1" works.

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    use alias in source sql statement so that it will not affect sql server.

    eg:

    select T1.#c123 as "C123" from db2table

  9. #9
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Hi Guys, I tried both query suggestions below, but got the same error message.
    "Please consult the documentation for your specific DB2 version SQLSTATE 42702 SQLCODE:-206."


    Any more ideas?

    Thanks

  10. #10
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    did you try

    select "#c123" as "C123" from db2table

  11. #11
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Yes I did. Same Error

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    May check with IBM and Microsoft to see if they have newer driver to fix the issue. By the way, did you ever try provider from IBM?

  13. #13
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    can you query the table in DB2 from the DB2 console? Try to use the same query on the DB2 side (and also post the query here)

    I dont think this is anything to do with SQL server.

    Some links reg. that error.

    http://webdocs.caspur.it/ibm/web/udb...m0/sql0200.htm

    https://aurora.vcu.edu/db2help/db2m0/frame3.htm#sql0200

  14. #14
    Join Date
    Oct 2002
    Location
    Trinidad
    Posts
    52
    Thanks MaK, I'll have a read

Posting Permissions

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