BAT文件中的SQLCMD实用程序-如何在语法错误的情况下返回ERRORLEVEL [英] SQLCMD utility from BAT file - how to return ERRORLEVEL in case of syntax error

问题描述

当某些.sql文件包含语法错误时，如何从SQLCMD实用程序中获取％ERRORLEVEL％?这些文件创建存储过程.它们不会调用"raiseerror"，但是它们可以表示语法错误，因此我需要终止该过程. 但是它总是将％ERRORLEVEL％返回为0. 我尝试使用-b，-V和-m(以及它们的组合)，但没有任何效果像预期的那样.

这是我的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%
)

解决方案

您确实需要-b开关，但需要与enabledelayedexpansion一起使用，这样您就可以在循环内使用!errorlevel!并获得预期的结果./p>

在执行sqlcmd之前将setlocal enabledelayedexpansion放在任何位置，可能最好在批处理开始时或循环之前.另请注意，使用感叹号(!)代替百分号(%)，百分号表示使用延迟扩展.

[我也用if not errorlevel 0 …(没有!，也没有任何%:请参见help if)进行测试，但我没有得到想要的结果]

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

解决方案

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]

这篇关于BAT文件中的SQLCMD实用程序-如何在语法错误的情况下返回ERRORLEVEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！