BAT文件中的SQLCMD实用程序 - 如果语法错误，如何返回ERRORLEVEL

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.

当某些.sql文件包含语法错误时，如何从SQLCMD实用程序获取％ERRORLEVEL％？这些文件创建存储过程。它们不会调用“raiseerror”，但它们可能会出现语法错误，我需要终止该过程。但它总是将％ERRORLEVEL％返回为0.我尝试使用-b，-V和-m（以及它们的组合），但没有按预期对我有效。

Here's the code fragment of my BAT file.

这是我的BAT文件的代码片段。

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%
    IF %ERRORLEVEL% NEQ 0 EXIT /B %ERRORLEVEL%
)

2 个解决方案

#1

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

你确实需要-b开关，但与enabledelayedexpansion一起使用，你可以使用！errorlevel！在循环内部并获得预期的结果。

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.

在执行sqlcmd之前将setlocal enabledelayedexpansion置于任何位置，可能最好在批处理开始时或在循环之前。还要注意使用感叹号（！）而不是百分号（％），它表示使用延迟扩展。

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

[我也测试过如果不是错误级别0 ...（没有！，也没有任何％：看看帮助，但是我无法得到预期的结果]

#2

Thank you, here is the work batch script.

谢谢，这是工作批处理脚本。

@ECHO OFF
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!
IF !ERRORLEVEL! NEQ 0 EXIT /B !ERRORLEVEL!
)

#1