windows 使用批处理文件和 Sqlcmd 进行错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5795430/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error Handling with Batch File & Sqlcmd
提问by SuperNES
I have a batch file that runs some SELECT queries using sqlcmd, puts the results into text files, and uploads those files onto an FTP server. That's all working just the way it should, which is how I like things to work.
我有一个批处理文件，它使用 sqlcmd 运行一些 SELECT 查询，将结果放入文本文件，然后将这些文件上传到 FTP 服务器。这一切都按照它应该的方式工作，这就是我喜欢的工作方式。
I've been wondering about what I would do in the event of an error, though. Let's say someone changes the data structure of the database I'm hitting and doesn't notify me. If I ran a sqlcmd SELECT statement and dropped the result into a text file, I would just end up with a text file containing an error, which would then go straight to the FTP as if nothing was wrong. (I've tested this.)
不过，我一直在想，如果出现错误，我会怎么做。假设有人更改了我正在访问的数据库的数据结构并且没有通知我。如果我运行一个 sqlcmd SELECT 语句并将结果放入一个文本文件中，我最终会得到一个包含错误的文本文件，然后它会直接进入 FTP，就好像没有任何问题一样。（我已经测试过了。）
I would like to be able to check for errors coming from sqlcmd--timeouts, bad credentials, malformed query, etc etc, I'm just not sure how this is done or what the "best practice" is. I could always try to crawl the output text file and search for errors I think might happen, but this is problematic for any number of reasons.
我希望能够检查来自 sqlcmd 的错误——超时、错误的凭据、格式错误的查询等，我只是不确定这是如何完成的或“最佳实践”是什么。我总是可以尝试抓取输出文本文件并搜索我认为可能发生的错误，但由于多种原因，这存在问题。
Anyone have any experience with this that they'd care to share?
任何人都有这方面的经验，他们愿意分享吗？
采纳答案by dcp
You can check errorlevelreturned from SQLCMDto see if it failed.
您可以检查errorlevel从返回SQLCMD以查看是否失败。
sqlcmd -b <yourscript>
IF ERRORLEVEL 1 goto err_handler
goto done
:err_handler
REM handle the error here
:done
REM script completion code here
回答by C???
I would maybe start with putting return values in your SQL, like so:
我可能会首先将返回值放入您的 SQL 中，如下所示：
DECLARE @IsBored bit = 1
... do work ...
SELECT 0 -- Success!
You could take it a bit further and use TRY/CATCH blocks to trap errors and return an error code. With SQLCMD, you can use the return code from your SQL as the exit code of the application, like so:
您可以更进一步，使用 TRY/CATCH 块来捕获错误并返回错误代码。使用 SQLCMD，您可以使用 SQL 的返回代码作为应用程序的退出代码，如下所示：
sqlcmd -b -S ServerName -E -d DbName -q "EXIT(EXEC dbo.YourProc)"
-o "C:\Logs\output.log" -u
If you were managing your SQLCMD calls with something like a scheduler, you could take action based on returns codes from SQLCMD. Since you're just using batch files, I think you can do something like this:
如果您使用诸如调度程序之类的东西来管理 SQLCMD 调用，您可以根据来自 SQLCMD 的返回代码采取行动。由于您只是使用批处理文件，我认为您可以执行以下操作：
@ECHO OFF
sqlcmd -b -S ServerName -E -d DbName -q "EXIT(EXEC dbo.YourProc)"
-o "C:\Logs\output.log" -u
IF %ERRORLEVEL% NEQ 0 ECHO "Error"
Good luck!
祝你好运！
回答by knkarthick24
for %%G in (*.sql) do (sqlcmd /S %sqlhost% /d %sqldbname% -E -b -i "%%G" >> output.txt if ERRORLEVEL 1 exit)
Above code will loop through all the *.sql in a folder. If error encountered in any of the script , error will get logged in the output.txt file and it will stop the batch process immediately.
上面的代码将遍历文件夹中的所有 *.sql。如果在任何脚本中遇到错误，错误将记录在 output.txt 文件中并立即停止批处理。
回答by Spencer Rathbun
I built a minilanguage in python to solve a similar problem. Using the subprocess library, you can run your code through sqlcmd, then get the output and any error codes. Parse the output before putting it into your text file, and if necessary go through error repair states. These states can modify the code or options and retry sending them through sqlcmd. If all else fails, email a human.
我在 python 中构建了一个 minilanguage 来解决类似的问题。使用 subprocess 库，您可以通过 sqlcmd 运行您的代码，然后获取输出和任何错误代码。在将输出放入文本文件之前对其进行解析，并在必要时进行错误修复状态。这些状态可以修改代码或选项，并通过 sqlcmd 重试发送它们。如果所有其他方法都失败了，请给人发电子邮件。
Of course, since I was using python like that, I didn't bother with sqlcmd at all and just used the odbc python libraries for a direct connection to the database. I could rollback my transactions if I had a catastrophic failure, run it in interactive mode, or via a command file etc. etc.
当然，因为我是这样使用 python 的，所以我根本没有打扰 sqlcmd，只是使用了 odbc python 库来直接连接到数据库。如果发生灾难性故障，我可以回滚我的事务，以交互模式或通过命令文件等运行它。
That's a pile of work though. For more simplistic error checking, just add a filter to your pipeline, say grep or awk. Or roll your own with flex.
不过那是一堆工作。对于更简单的错误检查，只需在管道中添加一个过滤器，比如 grep 或 awk。或者用 flex 滚动你自己的。
