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.

enclose the column name with [] and import.

I cannot change the column names on the AS/400 side. Is there another way

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?).

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

Adding the [] sign to the destination name does not help.

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.

use alias in source sql statement so that it will not affect sql server.

eg:

select T1.#c123 as "C123" from db2table

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

did you try

select "#c123" as "C123" from db2table

Yes I did. Same Error

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?

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

Thanks MaK, I'll have a read