MAXERRORS in BCP Does'nt work

Hi,
I'm trying to use MAXERRORS option (-m switch) to force loading to continue
even though errors occur, but the load stop at the first error occur, all
batch is commited and no error is logged in error file (-e hint)
here is the commande I'm using :
exec master.dbo.xp_cmdshell 'bcp "DBName.dbo.TableName" in
"Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
Is there any people successfuly use MAXERRORS option ?
Thanks for your helpMark (sauvemark@.yahoo.com) writes:
> I'm trying to use MAXERRORS option (-m switch) to force loading to
> continue even though errors occur, but the load stop at the first error
> occur, all batch is commited and no error is logged in error file (-e
> hint)
> here is the commande I'm using :
> exec master.dbo.xp_cmdshell 'bcp "DBName.dbo.TableName" in
> "Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
> Is there any people successfuly use MAXERRORS option ?
Yes, I've used it successfully. But it depends on what sort of error you
get. What is the exact output you get?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Yes, I've used it successfully
Glad to here this, I thought this a bug

> What is the exact output you get?
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY
KEY constraint
I apreciate your help
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E969C5660BYazorman@.127.0.0.1...
> Mark (sauvemark@.yahoo.com) writes:
"Path\FileName.tab" -m1000 -e"Path\ErrorFile" -T -E -n -q -S"ServerName"'
> Yes, I've used it successfully. But it depends on what sort of error you
> get. What is the exact output you get?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mark (sauvemark@.yahoo.com) writes:
> Glad to here this, I thought this a bug
>
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of
> PRIMARY KEY constraint
OK, so the errors that MAXERRORS control are conversion errors within the
file. That is, you are supposed to provide a decimal value, but the
valiue BCP finds is not decimal, so it cannot convert.
This is a pure SQL error, and for a PK violation the standard rule
applies: the entire statement is rolled back. That is, the entire BCP.
Unless you set a batch size. Then only the batch(es) with the violating
key are dropped. (At least I believe that BCP will continue, but I
have not tested, and now the hour is late.)
Setting a batchsize of 1, would give you all rows that does not violate
the PK, but that is likely to be effecient. Better is probably to use
a staging table, and then clean up the data in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx