batch-file - 批处理文件中的错误级别不起作用

Question

我正在尝试使用带有批处理脚本的 sqlcmd 删除数据库。我面临的问题是，如果 mytest 数据库在 sql 中不可用，则代码不会进入错误处理程序 block 。

@echo off
@echo IF ELSE DEMO
sqlcmd -S <servername> -U <username> -P <password> -Q "DROP DATABASE Mytest"  
IF ERRORLEVEL 1 goto err_handler
goto done

:err_handler
echo handle the error here
goto end

:done 
echo script completion code here

:end
@echo %Message%
pause

它给出了以下输出:

IF ELSE DEMO
Msg 3701, Level 11, State 1, Server HERCULES, Line 1
Cannot drop the database 'Mytest', because it does not exist or you do not have permission.
script completion code here
ECHO is off.
Press any key to continue . . .

我的期望是:

1. 如果数据库可用，则应删除数据库并应进入 :done 代码；如果数据库不可用，则应进入 :err_handler block 。

2. 我不希望 Echo is off 消息。

Answer 1

来自 MS documentation :

Error Reporting Options

-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.

-m error_level

Controls which error messages are sent to stdout. Messages that have a severity level greater than or equal to this level are sent. When this value is set to -1, all messages including informational messages, are sent. Spaces are not allowed between the -m and -1. For example, -m-1 is valid, and -m -1 is not.

This option also sets the sqlcmd scripting variable SQLCMDERRORLEVEL. This variable has a default of 0.

-V error_severity_level

Controls the severity level that is used to set the ERRORLEVEL variable. Error messages that have severity levels greater than or equal to this value set ERRORLEVEL. Values that are less than 0 are reported as 0. Batch and CMD files can be used to test the value of the ERRORLEVEL variable.

您将需要使用适当的值从数据库中获取错误。没有它们，您看到的 errorlevel 是指执行的 sqlcmd 实例(应该是 0 因为它已成功执行)，而不是命令在数据库中运行。

此外，如前所述，ECHO 已关闭。 消息由您生成

echo %Message%

变量未定义。该行被解析并转换为

echo 

因为 %Message% 没有值(value)。而不带参数的echo命令的行为是输出echo状态。