BCP - help with import/export using collation Chinese_PRC_CI_AS
am 03.05.2007 15:45:43 von InvestorTradeHi,
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:
80
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 çæ¼æ=80
13056 çææ´=80
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.