Take the 2-minute tour ×

How can I get %ERRORLEVEL% from SQLCMD utility when some of .sql files contains syntax error? These files create stored procedures. They don't invoke "raiseerror", but they can conatin syntax error and I need to terminate the process. But it always returns %ERRORLEVEL% as 0. I tried use -b, -V and -m (and their combinations), but nothing worked for me as expected.

Here's the code fragment of my BAT file.

REM process all sql files in "SQL\scripts" folder and subfolders
FOR /R "SQL\scripts" %%G IN (*.sql) DO (
    sqlcmd -d %1 -S %2 -U %3 -P %4 -i "%%G" -b -V 1

    echo %ERRORLEVEL%
share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

You do need the -b switch but together with enabledelayedexpansion, that way you can use !errorlevel! inside the loop and get the expected results.

Put setlocal enabledelayedexpansion anywhere before you execute sqlcmd, probably best at the beginning of the batch or just before the loop. Also note the use of exclamation points (!) instead of the percent signs (%), which denote the use of delayed expansion.

[I also tested with if not errorlevel 0 … (no !, nor any %: see help if) but I could not get the desired results]

share|improve this answer
Thank you, this is exactly what I needed to know. –  kovalu Apr 27 '11 at 10:05
add comment

Thank you, here is the work batch script.

setlocal enabledelayedexpansion
FOR /R "C:\SQL" %%G IN (*.sql) DO (
sqlcmd -S%1 -d tangoDB -E -h-1 -w255 -i "%%G" -b
echo   %%G  -  !ERRORLEVEL!
share|improve this answer
add comment

Your Answer


