T-SQL清除错误 [英] T-SQL Clear Errors
问题描述
例如，存储的proc检测到丢失的数据，并执行以下操作：
如果@CompanyID为null raiserror（'@CompanyID缺少'，18，1）;
开始捕获逻辑处理某些错误（即将其记录到错误表），然后做一个返回0。我相信以前的程序员认为这会让BizTalk再次被呼叫。但现在我有一个痕迹，我可以证明BizTalk正在调用它x每次y分钟重试（由BizTalk SendPort定义）。我目前的想法是，如果@@ Error和@@ ErrorMessage不为空，BizTalk仍然认为它是错误的，并将重试。有关如何彻底清除错误的任何想法？我希望做一个小的快速修复，没有时间进行重大改写。
我认为这篇文章基本上回答了我的问题（这也是我放大了关于错误处理内容的澄清一个catch块）：
T-SQL是在BEGIN CATCH中完全关闭的错误处理。块？
尝试中的某些语句不会炸毁。他们可以设置错误，代码不断下降。
在这种特定情况下，catch块有一个Insert插入到一个表中，一列已经在存储过程中展开，但不在错误表中。我们在大概4个月之前在其他表格中扩展了这个列，但是我们从来没有在错误处理程序中遇到这个隐藏的隐藏问题，直到本周。
我认为其他StackOverflow文章最终将有助于回答这个问题，为什么BizTalk看到这是一个错误。我会认为返回0会这样做。但是由于我们收到截断问题，所以发送到BizTalk的错误。
在现实世界中，我们很少遇到这个错误代码，当我们这样做时，导致错误的特定列不是太大。当质量保证人开始一定的测试场景并且将一些无效的航班号码与违规列的特别长的值相结合时，出现这种错误。
I'm trying to clean up a huge legacy T-SQL stored proc. We call it from BizTalk. BizTalk has a feature to retry a send port (for example 3 times every 5 minutes) when it is in error.
For example, the stored proc detects missing data and does this:
if @CompanyID is null raiserror( '@CompanyID is missing', 18, 1 );
The "Begin Catch" logic handles certain errors, (i.e. logs them to error tables), and then does a "Return 0". I believe the prior programmer thought this would keep BizTalk from calling it again. But now I have a trace and I can prove that BizTalk is calling it x retries every y minutes apart (as defined by the BizTalk SendPort).
My current thought is that if @@Error and @@ErrorMessage are not null, BizTalk still thinks it is in error, and will retry.
Any thoughts on how to totally clear the errors? I was hoping to do a small quick fix, no time for a major rewrite.
I think this post basically answers my question (this is also a post by me to zoom into clarification relating to error handling inside a catch block):
T-SQL is error handling totally turned off in a "BEGIN CATCH" block?
Certain statements in a try catch do not blow up. They can set the error and the code keeps on falling through.
In this specific case, the catch block had an Insert into a table, and one column had been expanded in the stored proc, but not in the error table. We expanded the column in other tables probably 4 months ago, but we never caught this insidious hidden issue with the error handler until this week.
I think that the other StackOverflow post will eventually help answer the question as to why BizTalk see this as an error. I would think "return 0" would do it. But since we were getting the truncation problem, that error was being sent to BizTalk.
In the real world, we rarely hit this error code, and when we do, the the particular column that was causing the error was not too big. This error arose when a QA person began a certain test scenario and was putting in some invalid flight numbers in conjunction with a particularly long value for the offending column.
这篇关于T-SQL清除错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
