Posted by InvestorTrade on 05/03/07 13:45

Hi,

I've been trying to export/import a table from a DB with
Chinese_PRC_CI_AS collation, and I can't seem to get it working at all
- the export seems rather simple, it is the import that is not working
at all. I am using MSSQL 2000:

The table (under a Chinese_PRC_CI_AS collation DB) is as follows:


CREATE TABLE [dbo].[test_table] (
[id] [int] NOT NULL ,
[first_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:

BCP "select * from testdb.dbo.test_table" queryout test_table.txt -c -
t -S"10.10.10.10" -U"sa" -P"mypassword"

(This is how I must to run the export - I can not change the way of
the statement is run - the reason is because I am using someone elses
exports)

No I try import the data back into the same table, test_table, but
first I take the following steps:

from iSQL: "DELETE FROM testdb.dbo.test_table"

Then I create a format file, test_table.fmt, which looks like this:

8.0
2
1 SQLINT 0 12 "" 1 id Chinese_PRC_CI_AS
2 SQLNCHAR 0 100 "\r\n" 2 first_name Chinese_PRC_CI_AS

So here is the problem:

When I import the data using the command

BCP "testdb.dbo.test_table" in "test_table.txt" -f"test_table.fmt" -
S"10.10.10.10" -U"sa" -P"mypassword"

The rows get imported - but they are nothing like the data that I
imported:

test_table.txt contents:

1
pete
2
rob
3
sam
4
carl

Imported results:

12800 爀漀戀
13056 猀愀洀
13312 挀愀爀氀
3276543 瀀攀琀攀

I've tried changing the format file line's separator to be as
follows: "\n", "\n\0", "\r\0\n\0" - but the results did not differ
much from each other.

Any help is greatly appreciated.

Thank you,

Jim.

[Back to original message]