bcp within batch file

[DBA]

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!

[Erland Sommarskog]

DBA (ka******@yahoo.com) writes:

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

The return status for a program called from a batch file is in
%ERRORLEVEL%, so this is the variable you should check.

I seem to recall that BCP does not always set this variable as one
may desire. It does set it, if the password is wrong. But I believe
it does not set %errorlevel% if some rows does not load.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DHatheway]

Looks like most interesting bcp errors will set %errorlevel% to 1. An empty
input file, however, doesn't set the errorlevel. You can take action in a
..CMD file like this:

bcp <table> [in|out] <filespec> [switches]
if %errorlevel% 1 goto <label>
<normal processing steps here>

:<label> echo something BAD happened to your BCP!
<steps to do something about it here>

I didn't test it but I believe if you set the maxerrors switch, you won't
get the non-zero errorlevel unless you actually exceed that threshold. You
might want to test this yourself.

FYI - in our .CMD scripts, if I want to simply fail the job after the error,
I usually do this:

bcp <stuff>
if %errorlevel% 1 goto BCP_FAILED

and I don't bother using a BCP_FAILED label anywhere. Searching for it, the
job runs right past the end and aborts. You'll see a message saying "Can't
find label BCP_FAILED" or something similar as part of the job status report
if you run this through SQL Executive and, by convention here, that's the
diagnostic for the job.

"DBA" <ka******@yahoo.com> wrote in message
news:ff**************************@posting.google.c om...

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!

[John Bell]

Hi

%ERRORLEVEL% will be 0 when a succesful import has been performed. If it
fails then it will return 1 (on my tests!).

John

"DBA" <ka******@yahoo.com> wrote in message
news:ff**************************@posting.google.c om...

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!