BCP Issue w/Right Truncation

Hello All,

I am attempting a bulk load of fixed position flat file data via bcp
and I have noticed that I get a Right Truncation error when trying to
load a row where the last column value is NULL.

For example:

Flat file row:
0000016M

FMT file:
7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 1 "\r\n" 3 HEIGHT

In this row, the height info is null and I get a right truncation
error. The row below, with height info goes in fine:

Flat file row:
0000016M510

Let me know what I am doing wrong!

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I am attempting a bulk load of fixed position flat file data via bcp
> and I have noticed that I get a Right Truncation error when trying to
> load a row where the last column value is NULL.
> For example:
> Flat file row:
> 0000016M
> FMT file:
> 7.0
> 3
> 1 SQLCHAR 0 7 "" 1 RECORD_KEY
> 2 SQLCHAR 0 1 "" 2 SEX
> 3 SQLCHAR 0 1 "\r\n" 3 HEIGHT
> In this row, the height info is null and I get a right truncation
> error. The row below, with height info goes in fine:
> Flat file row:
> 0000016M510

It would have help if you had posted a CREATE TABLE statement for your
table, and also provided a sample file where the is a row that fails.

But maybe you should try this file instead:

7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 3 "" 3 HEIGHT
4 SQLCHAR 0 0 "\n" 0

(I believe that \r\n or \n are the same here.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp