Error Handling with Batch File & Sqlcmd

Question

I have a batch file that runs some SELECT queries using sqlcmd, puts the results into text files, and uploads those files onto an FTP server. That's all working just the way it should, which is how I like things to work.

I've been wondering about what I would do in the event of an error, though. Let's say someone changes the data structure of the database I'm hitting and doesn't notify me. If I ran a sqlcmd SELECT statement and dropped the result into a text file, I would just end up with a text file containing an error, which would then go straight to the FTP as if nothing was wrong. (I've tested this.)

I would like to be able to check for errors coming from sqlcmd--timeouts, bad credentials, malformed query, etc etc, I'm just not sure how this is done or what the "best practice" is. I could always try to crawl the output text file and search for errors I think might happen, but this is problematic for any number of reasons.

Anyone have any experience with this that they'd care to share?

Answer 1

You can check errorlevel returned from SQLCMD to see if it failed.

    sqlcmd -b <yourscript>
    IF ERRORLEVEL 1 goto err_handler
    goto done
    :err_handler
    REM handle the error here

    :done 
    REM script completion code here