Error Handling with Batch File & Sqlcmd

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?

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

I would maybe start with putting return values in your SQL, like so:

DECLARE @IsBored bit = 1

... do work ...

SELECT 0 -- Success!

You could take it a bit further and use TRY/CATCH blocks to trap errors and return an error code. With SQLCMD, you can use the return code from your SQL as the exit code of the application, like so:

sqlcmd -b -S ServerName -E -d DbName -q "EXIT(EXEC dbo.YourProc)" 
       -o "C:\Logs\output.log" -u

If you were managing your SQLCMD calls with something like a scheduler, you could take action based on returns codes from SQLCMD. Since you're just using batch files, I think you can do something like this:

@ECHO OFF
sqlcmd -b -S ServerName -E -d DbName -q "EXIT(EXEC dbo.YourProc)" 
       -o "C:\Logs\output.log" -u
IF %ERRORLEVEL% NEQ 0 ECHO "Error"

Good luck!

for %%G in (*.sql) do (sqlcmd /S %sqlhost% /d %sqldbname% -E -b -i "%%G" >> output.txt if ERRORLEVEL 1 exit)

Above code will loop through all the *.sql in a folder. If error encountered in any of the script , error will get logged in the output.txt file and it will stop the batch process immediately.

I built a minilanguage in python to solve a similar problem. Using the subprocess library, you can run your code through sqlcmd, then get the output and any error codes. Parse the output before putting it into your text file, and if necessary go through error repair states. These states can modify the code or options and retry sending them through sqlcmd. If all else fails, email a human.

Of course, since I was using python like that, I didn't bother with sqlcmd at all and just used the odbc python libraries for a direct connection to the database. I could rollback my transactions if I had a catastrophic failure, run it in interactive mode, or via a command file etc. etc.

That's a pile of work though. For more simplistic error checking, just add a filter to your pipeline, say grep or awk. Or roll your own with flex.