Posted by Erland Sommarskog on 05/03/07 22:14

InvestorTrade (shija03@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]