How to detect BCP success/fail in batch file?

We are calling BCP from a batch file but cannot figure out how to detect
whether it was successful or if it failed. With osql we use EXIT() with a
value to indicate success or failure. Is there a way to do this with BCP? Or
does it report success or failure to the calling process or set an operating
system error value?
TIA
Michael MacGregor
Database Architect
Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> We are calling BCP from a batch file but cannot figure out how to detect
> whether it was successful or if it failed. With osql we use EXIT() with
> a value to indicate success or failure. Is there a way to do this with
> BCP? Or does it report success or failure to the calling process or set
> an operating system error value?
BCP does indeed set %ERRORLEVEL%. However, your and BCP's idea of what
is an error may not be the same. BCP will return with an error status
if for instance the data file cannot be found. It may also set a return
status if the end of file is in the middle of a record (which usually
means that your format specification is wrong). But I seem to recall
that if one more rows fail to import, that it does not set ERRORLEVEL -
not even if all records fail.
You can use the -e option to get error information into a file, this
captures problems with individual records.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or fails
(failure caused by PK constraint), the value is the same, 0. However I
realised that I might not be using the latest version of BCP and checked, it
was BCP v8, so I updated my PATH to use BCP v9 and then it sets the
ERRORLEVEL correctly.
So I guess it's best to use the latest version.
Michael MacGregor
Database Architect
|||Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or
> fails (failure caused by PK constraint), the value is the same, 0.
> However I realised that I might not be using the latest version of BCP
> and checked, it was BCP v8, so I updated my PATH to use BCP v9 and then
> it sets the ERRORLEVEL correctly.
Interesting. That's seems to be an improvement.
However, I would test a lot more error situations to see if all set
ERRORLEVEL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I've got round the immediate problem by using a SQL script incorporating
BULK INSERT. It does the job with the error handling I need.
Although I will take your advice under due consideration as I have another
process that needs to be "error aware" that also uses BCP right now, and
also is using v8.0. So I will have to update that to v9.0 and test it to
make sure it catches the types of errors that tend to occur.
Thanks.
Michael MacGregor
Database Architect