?
Solved

trying to capture BCP error from a Korn Shell

Posted on 2005-04-08
13
Medium Priority
?
953 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:pdadddino
  • 8
  • 5
13 Comments
 
LVL 8

Expert Comment

by:koppcha
ID: 13738532
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
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13738545
you can use bcps -e option to write the errors in to a file and grep it as well
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13738614
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
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 

Author Comment

by:pdadddino
ID: 13739123
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?
0
 
LVL 8

Accepted Solution

by:
koppcha earned 2000 total points
ID: 13739210
my first case should work

redirect the output of bcp to a file like
bcp -U -P >> filename

status=$(egrep -c 'Msg|ERROR' filename)
if (($status == 0))
then
print "Success"
else
print "Failure"
fi
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13739235
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.
0
 

Author Comment

by:pdadddino
ID: 13739661
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?
0
 

Author Comment

by:pdadddino
ID: 13739708
maybe i can use your egrep loop, koppcha, and search for "fail"?
0
 

Author Comment

by:pdadddino
ID: 13739736
btw-what is the -c option mean for egrep?
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13739750
>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.
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13739764
>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
0
 

Author Comment

by:pdadddino
ID: 13739830
thanks for the help
0
 
LVL 8

Expert Comment

by:koppcha
ID: 13739842
No problem .Thanks for the points


Good Luck
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question