Another day, another bug to resolve. This time, Mark C. was tasked with figuring out why users of their behemoth, in-house disaster of an application were getting the following message from time-to-time:

We're sorry, but your request cannot be processed at this time for the following reason(s):

Unable to delete
This sale has been previously approved.

With the only indication of source page behind this error being the URL "/error.asp?msg=Unable+to+delete%0A+This+sale+has+been+previously+approved.", Mark had little choice but to do a full-text through the entire code base. Twenty minutes and several permutations of the error message later, and BINGO! It was tucked in the several-thousand-lines-long Errors.vb: 

1823:    Case Is = "82X"
1824:       slError = "Unable to delete\nThis sale has " & _
1825:                 "been previously approved."

Obviously, this was one of the many errors crafted during the application's "let's centralize all error messages and call them only by their three-digit code" phase. Mark ran another full-text search and eventually stumbled upon the code that was probably responsible for raising error 82X. It was buried in sp_utl_CheckApproval_SubSA.sql: 

467    SET @ErrCde = '82'
468
469    IF @OrdStat = 'PND' SET @ErrCde = @ErrCde + 'B'
470    ELSE IF @OrdStat = 'CNL' SET @ErrCde = @ErrCde + 'C'
471    ELSE IF @Dscnt < @MAX_DSNT SET @ErrCde = @ErrCde + 'F'
-- snip --
488    ELSE IF @ApprCt > 0 SET @ErrCde = @ErrCde + 'X'
-- snip --
498    EXEC sp_utl_ErrorText @ErrorMessage OUTPUT, @ErrCde

Although his next step should have been yet another full-text search for "sp_utl_CheckApproval_SubSA", Mike was curious. He just had to peek in to the sp_utl_ErrorText procedure first. What could it possibly be doing with "82X"?

As it turned out, sp_utl_ErrorText used SQL Server's built-in sp_OACreate and sp_OAMethod procedures to instantiate a COM object ("CommonUtil.Errors", the compiled instance of Errors.vb) and execute its "DecodeErrorCode" method. Mike had no idea that it was even possible to work with COM objects from within a stored procedure, let alone that such a technique was used by his very own day-to-day nightmare. And although he should have just continued debugging the error message at hand, his morbid curiosity got the best of him once again. He ran another fulltext search, this time for "sp_OACreate". 

Token "sp_OACreate" found in 84 file(s)...

Fortunately, he stopped his search there. Digging into those stored procedures -- only to see them call middle-tier VB code, which would in turn call other stored procedures, which would call middle-tier code, then stored procedures, then middle tier, ad infinitum -- would have further confirmed that he was, in fact, living in the 9th Circle of Hell.

Mike got back and track and started the search for the code that called sp_utl_CheckApproval_SubSA. It was called from a VB middle-tier DatabaseCommunications component, which was called (via COM+) from another middle-tier component, which was wrapped by the BusinessTransactionManager component, which was wrapped by a web service, which was, in turn, called by the WebServiceManager from the web application, which had a small validation bug, thus allowing the error to occur.

Mike tweaked the validation logic -- changing a single "Or" to an "And" -- compiled, tested, and checked in the new code. Six hours after starting on the bug, he had resolved it and was ready to go home.

It was a pretty satisfying day. Mike figured that he was probably only on the 4th, maybe even 3rd, Circle of Hell.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!