BCP - help with import/export using collation Chinese_PRC_CI_AS

[InvestorTrade]

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.

[Erland Sommarskog]

InvestorTrade (sh*****@gmail.com) writes:

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"
...
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

The format file does not match the command that generates the file.
The export command is for a tab-delimited text file, your format file
is for a binary file. A format file for a text file uses only
SQLCHAR for the data type (or SQLNCHAR if it's a Unicode file).

Thus the correct format file would be:

8.0
2
1 SQLCHAR 0 0 "\r\n" 1 id Chinese_PRC_CI_AS
2 SQLCHAR 0 0 "\r\n" 2 first_name Chinese_PRC_CI_AS

Although I suspect that you could simply use -c to import the file.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx