trapping bcp errors

Question

trapping bcp errors

Green Armchair

Hall of Fame

Points: 3561
More actions
January 19, 2004 at 5:28 am

#160599

Hi
Is it possible to trap a bcp error through %errorlevel%?
If a bcp fails, it seems to return 0 in %errorlevel%. Is there anyway to get bcp to return non-zero if it fails?
Thanks
Andy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 1

I never found a way.

As a work around I let awk (gawk actually) check the output file for the regular expression /Msg/||/[Ee]rror/ and exit awk with an error code if it found something. It works, but is far from elegant.

Joachim.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

My preference was to use VBScript and use the SQLDMO and BulkCopy objects, then use error trapping in VBScript.

Mark

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

mharr

Thanks for your reply. Would you be prepared to let me have a sample piece of vbscript?

Andy

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

This is a link to template file I used on a project for sending table updates to multiple clients. It demonstrates using vbscript to use DMO objects to backup the database, create temporary user tables to bulk insert data into, then drop the production tables and rename the temporary tables to the production names. The scenario for this was the client was sending full table replacements each time, for tables that were only updated this way (users not update tables directly), and this script provided us to be able to update these tables without users being impacted. These tables were large, one in particular could be 1.5-2.0 million rows.

http://www.harr.us/UpdateTemplateScript.zip

This script does not have the error handling, as it was designed to run from command prompt using cscript.exe. Error handling that you are wanting would involve wrapping code around each statement you wanted to trap, like this:

On Error Resume Next
nRows = oDatabase.Tables("CPTFreq_Temp").ImportData(oBCP)
If Err.Number > 0 Then
 wscript.echo time & " Import Failed! script is aborting"
 wscript.echo Err.Source & " (" & Err.Number & "): " & Err.Description
 wscript.echo Quit (1)
Else
 wscript.echo time & " Imported " & nRows & " rows."
End If

Notice that you can return an errorcode with the .Quit method, to be used if the script is run by a command/batch file.

Hope this helps.

Mark

Green Armchair Hall of Fame Points: 3561 More actions · Answer 5

Thanks very much for this. I'll get back to the developer to see how they want to procede. The vbscript will be very usefull if they want to go down this route.

If only bcp returned an error code!

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

My test shows that if bcp fails ir returns 1 as error.

therefore here is a script:

bcp MyDB..MyTableWichNotExists out c.txt -SMyServer -UMyUser -PMyPassword -c -C1250 -r\n -t\t

@if errorlevel 1 echo bad command

Bye
Gabor

Green Armchair Hall of Fame Points: 3561 More actions · Answer 7

Interesting. If you're using bcp to import and you get a constraint error, bcp doesn't report an error!!

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 8

You're right.

I've tested it.

And the most interesting thing is the the error file (-e switch) is not filled in.

Bye
Gabor