I have a text file whos rows are terminated with 0A (NewLine). When I try to
bcp IN these rows using -r"\n" which is the NewLine character it yields a
"right truncation" error. When I recreate the file with 0D0A, the file loads
properly, even though I use the same -r parameter. It seems bcp requires the
0D (Carriage Return) even though it is not specified in the -r parameter. I
have tested this thoroughly and it indeed does behave this way on SS2000. So
how do I get the file to load when terminated with only the 0A?

Thanks

Snake (Snake (AT) discussions (DOT) microsoft.com) writes:
You need to use a format file. In a format file, \n means \n and not
\r\n.

Say that your file is tab-separated with four fields, equally many as
in your target table. Then your format file should look like this:

8.0
4
1 SQLCHAR 0 0 "\t" 1 col1 ""
2 SQLCHAR 0 0 "\t" 2 col2 ""
3 SQLCHAR 0 0 "\t" 3 col3 ""
4 SQLCHAR 0 0 "\n" 4 col4 ""




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx