Using the Max_Errors parameter with the BCP utility

microsoft.public.sqlserver.tools

10 Sep 2007 4:41 PM

Nick

I am trying to use the BCP command to load data from text files into
SQL Server 2005. In my scenario, if any of the data contains a syntax
error, then I want no records added to the table in the database.

For example, to load the data I am currently executing the BCP command
with the following parameters:

bcp "Database..DATEFLAGT" in "C:\TextDataFiles\DATEFLAG.txt" -m1 -f"C:
\FormatFiles\DATEFLAG.fmt" -e "C:\FormatFiles\DATEFLAG.bad" -b=50000 -
S<server\instance_name> -U<userid> -P<password> -h"CHECK_CONSTRAINTS"

If the text file to be loaded has invalid data in the second row, the
first row is still being loaded to the database successfully. I don't
want that to happen. In my scenario, I either want all the data
loaded, or none.

I understand that the first row is being loaded because I have the
parameter set up as -m1, meaning it will allow up to one syntax error.
But -m0 doesn't work either as I believe that means load all the data
regardless of the number of syntax errors found.

What changes do I need to make to my BCP command to work for my
criteria of loading all the data, or none, if any errors are found?