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

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:
Interesting. That's seems to be an improvement.

However, I would test a lot more error situations to see if all set
ERRORLEVEL.

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

Import data to sql Server 6.5
one-to-one relationships in a database diagram
Database copy
How to create a single reusable alert in SQL Server Agent?
Square Brackets