T-SQL Clear Errors

Question

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.

Answer 1

This may not be applicable but if you increase the error level this may fix the issue by causing a more serious error to be logged. Generally 1 through 19 I believe are more informational related and after 20 is considered a fatal error and will log the error in the SQL Log as well. This may force the program to stop even if a handler is still believing it to be open. You may always test this and see if it suits your needs and change it back if not. You may also try a more proper BEGIN END blocking too and a generic return not returning a value for the catchs like:

IF @CompanyID is NULL
  BEGIN
     Raiserror('@CompanyID is missing', 20, 1);
     return;
  END

More on error catching here: http://msdn.microsoft.com/en-us/library/ms178592.aspx

Answer 2

If the error handling is SQL-based then it sounds like you may want to replace your RAISERROR completely and instead log your error at that point and simply return.

Also, @@Error will never be null. It will return a "new" value with each statement that is executed. If the previous statement is executed successfully, it will return 0, otherwise it will return the error number....which in your case would be 18. If your logic is keying off of @@Error, then you need to make sure it is being checked (or saved into a variable) immediately after the statement in question executes.

More info below.

http://technet.microsoft.com/en-us/library/ms190193(v=sql.100).aspx

Answer 3

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.