Q:

Error testing and control from DOS

I'm running DOS 6.0.6002 on a windows server enterprise system, SP2.
SQL Server 2008 R2  (10.50.4000)
I have a main control program in DOS.
I'm invoking an sql program through sqlcmd.
A simplified version looks like this:
set sqlsvr=myServer
set logfile=logfile.txt
sqlcmd -S %sqlsvr%  -d myDB -i import_some_stuff.sql > "%logfile%" 2>&1
echo error level = %ERRORLEVEL%

I need this program to be pretty robust.  It has to run every day against a lot of files and tables.  If it fails, I need to catch it and notify sysadmin.  For now, just catch it.
So to test this, I've tried the following tests:
1) Renaming the file to one that does not exist.
  Result: it returns and errorlevel of 1 (that is it caught the error!) bravo!
2) typing in some syntactical rubbish at the front of the sql program.
  Result: it prints the error message in the log file, BUT it DOES NOT return an error (so the return value in %ERRORLEVEL% is zero.  This seems incredible to me.  What am I missing?

A:

Try the -b option to sqlcmd:

-b
Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an 
  error occurs. 
  The value that is returned to the DOS ERRORLEVEL
  variable is 1 when the SQL Server error message has a severity level
  greater than 10; otherwise, the value returned is 0. If the -V option
  has been set in addition to -b, sqlcmd will not report an error if the
  severity level is lower than the values set using -V. Command prompt
  batch files can test the value of ERRORLEVEL and handle the error
  appropriately. sqlcmd does not report errors for severity level 10
  (informational messages).
If the sqlcmd script contains an incorrect comment, syntax error, 
  or is missing a scripting variable, ERRORLEVEL returned is 1.

Here is the documentation

