As our little summer break continues, we have a tale about Remi (no relation) and a missing stored procedure. Original --Remy

Remi works on one of his country's largest Internet Service Providers, and has the fortune to be on an elite team that focuses on agile development. Or misfortune, depending on how you look at it: at his company, "agile development" actually means "we need that in two weeks".

One of Remi's first assignments was to fix an "emergency" on one of the ATM Addressing systems. Apparently, the application was coming up with incorrect routing data. After a solid day-and-a-half of digging through Visual Basic code that called SQL Server stored procedures which called VB-based COM objects which called more stored procs, Remi found a weird table ("Cal_ATM") that was referenced from an externally-linked database, and the data in that table was completely out of date.

Wondering how that table was populated, Remi searched high-and-low through the VB and stored proc code, but found nothing. Thinking it might be directly updated by some batch job, he looked through the job lists, and that's where he found it: a step buried in a job that called "EXECUTE PUpd_Cal_ATM". As it turned out, the step had been erroring-out for nearly a month-and-a-half with "Could not find stored procedure PUpd_Cal_ATM". No one knew about it, of course, because the failure notification was also not working.

The timing of the failures seemed to coincide with a recent "emergency" clean-up effort that another developer on the agile development team performed, and Remi was worried that this procedure was accidently deleted during the clean-up. He searched for a backup that was made before the clean-up project, and was able to restore the missing procedure. Its entire code was as follows.

CREATE PROCEDURE [dbo].[PUpd_Cal_ATM]
AS
-- +---------------------------------------------------------------------------+
-- | Description : Feeds the tables ATM                                        |
-- | Entry : none                                                              |
-- | Output : Return (0 = OK)                                                  |
-- +--------+------+-------+---------------------------------------------------+
-- | Date   |Author|Version|Description                                        |
-- +--------+------+-------+---------------------------------------------------+
-- |06/12/07| XXX  | 1.0   | Starting version                                  |
-- +--------+------+-------+---------------------------------------------------+

-- For now, this stored procedure is empty but it may be 
-- called by UpdateDatabase
-- ==========================================================================
-- return code
-- ==========================================================================

It made little sense; this procedure had clearly not been feeding the database for three years, yet the data was only a week old. It took him three more full days to figure out the source: a hotfix to Microsoft Office caused a certain type of macro code in Excel to be considered insecure, and a certain type of macro was being used to feed the ATM Addressing system from Excel.

Oh, the joys of agile development.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!