We are trying to use BCP to import data from flat files into tables in a SQL
2008 database. Even though BCP is erroring out, it is still putting data
into the table. Doe anyone know of a fix to this?

bcp <tablename> in <filename> -S <Server> -T -h "CHECK_CONSTRAINTS" -c -m 1
-F 2 -e err.txt

When we run the above statement, the following is displayed:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right
truncation

540 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 32 Average : (16875.00 rows per sec.)

All of the rows prior to the error were written to the database. I have
tried adjusting the batch size, but nothing I have tried has worked at
avoiding the writing of rows.

If I use the BULK INSERT statement with similar parameters and file, it
stops at the 1st error and writes no data to the file.

Thanks in advance for your assistance,
Dan

DanH (DanH (AT) discussions (DOT) microsoft.com) writes:
AS a matter of fact when I test, also later rows are inserted.

I found this in Books Online in the description of the -m option:

The max_errors total excludes any errors that can be detected only at the
server, such as constraint violations.

Thus, I think you are fighting an uphill battle.


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