...catch your error: Combine SQL RAISERROR and Batch File SQLCMD ERRORLEVEL

Problem

Your batch file has SQLCMD commands and you want to view/log the SQL execution errors. This is what worked for me:

Solved

1.     Add RAISERROR to your SQL script to return error level and message, ideally in a BEGIN CATCH block. Here, I’ll show you mine:

            ...

END TRY

            BEGIN CATCH
                  DECLARE @ErrMsg VARCHAR(200);
                  SET @ErrMsg = ERROR_MESSAGE();  
                  RAISERROR(@ErrMsg,16,1)
          END CATCH

2.     Then add command line option –b to your SQLCMD command line, something like this:

SQLCMD -E -S SERVERNAME –i "C:\MyFolder\MyScript.SQL" -b > C:\MyFolder\SQLErrorTest.LOG

  

3.     Lastly catch the error in your batch file, note enclose ERRORLEVEL with %

IF %ERRORLEVEL% NEQ 0 (GOTO YourErrorProcess)

Run your batch file and view your LOG file for errors, if any...