Post reply

trapping bcp errors

  • 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

  • January 19, 2004 at 8:11 am

    #490682

    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.

  • January 20, 2004 at 7:01 am

    #490802

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



    Mark

  • January 20, 2004 at 8:06 am

    #490833

    mharr

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

    Andy

  • January 20, 2004 at 9:01 am

    #490840

    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

  • January 20, 2004 at 9:28 am

    #490848

    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!

  • January 22, 2004 at 1:50 am

    #491075

    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

  • January 22, 2004 at 2:00 am

    #491077

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

  • January 22, 2004 at 3:15 am

    #491080

    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

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

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