asked on

trying to capture BCP error from a Korn Shell

Am doing many bcp's in a ksh script on sun enterprise box(solaris 5).
right now im using sybase 11.9.2 soon to convert to 12.5.3.
im trying to capture error messages from bcp utility
no examples
and from sybase during bcp:
example
Msg 2601, Level 14, State 3:
Attempt to insert duplicate key row in object 'interm_ext_pos' with
unique index 'idx1'

not sure if its possible but when bcp fails it causes major production issues?

koppcha

I have the same thing at my work place this is what i do

I am redirecting the output of bcp to a file and grep it for Msg .If it is found then stop to bcp further tables

koppcha

you can use bcps -e option to write the errors in to a file and grep it as well

koppcha

you can also do this assuming you are using korn shell and keep this in a loop so that you can exit when any BCP is not successful

bcp <> out <> -U -P -S
if (($? != 0))
then
print "There is error in BCP out"
exit
END

pdadddino

ASKER

thanks for the quick response. i have been trying the -e option.
but in my latest case the errors are from sybase so this doesnt help.
im thinking about doing a rowcount from the table before and after the bcp and the diff should be the line count from the bcp file. this sounds like it will work in all cases.
any thoughts?

ASKER CERTIFIED SOLUTION

koppcha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

koppcha

you can keep the whole thing in a loop and automate it for all the tables you want to BCP out . so that you do not have to check again and again for the success.

pdadddino

ASKER

my only concern is some messages such as:
DB-LIBRARY error:
Attempt to bulk-copy an oversized row to the SQL Server.

this error message does not cause the bcp to fail. This script runs in the middle of a overnight batch so i dont want to bail out on these types of situations?

pdadddino

ASKER

maybe i can use your egrep loop, koppcha, and search for "fail"?

pdadddino

ASKER

btw-what is the -c option mean for egrep?

koppcha

>this error message does not cause the bcp to fail
you can only stop the next bcp statement that is going to come you can modify the egrep by
egrep -c 'Msg|DB-LIBRARY' filename so that it would stop the next BCP statement.

koppcha

>w-what is the -c option mean for egrep?
No of matching if there is one match it would return 1 .
I am comparing that with zero.If it is zero then the statement was success

pdadddino

ASKER

thanks for the help

koppcha

No problem .Thanks for the points

Good Luck