| One of my employers sells packages to events, such as U2's current Vertigo concert tour. Many clients and prospects visit the Web site to buy a package, so we decided to give them a 10-minute window to complete the transaction. We devised a way to place a "hold" on the number of tickets requested and then release the tickets back into the available inventory if the 10-minute window expires. This is a great solution if your e-commerce site offers a limited number of items that often sell out quickly. First, I created a table called OnLineOrders, with two columns of interest: - DateTimeEntered: a DateTime column whose default is GetDate(). - HoldExpires: a calculated column whose formula is DateAdd(mi, 10, [DateTimeEntered]). For simplicity's sake, on subsequent queries regarding available inventory, I chose to add another column called IsHold (bit). In this application, deciding how much inventory is available involves several business rules of little general interest. There is a view (and a similar stored procedure) which determines the actual number by looking in several "bins" and then subtracting the tickets currently on hold for the Web site users. If the Web user doesn't complete a transaction in 10 minutes, the hold is released. To achieve this, I created a simple stored procedure called HoldsRelease_ap, which looks like this:
Follow these steps to schedule this procedure to run every 10 minutes: 1. Open Enterprise Manager, select the database and its Stored Procedures node. 2. Right-click in the Stored Procedures window and select New Stored Procedure. 3. Write the stored procedure, check its syntax, and save it. 4. On the Enterprise Manager menu, select Tools | Job Scheduling. This invokes a wizard whose first page contains an Option button set from which you'll want to select the Transact SQL Command. Click Next. 5. On the next page, in the big box, type EXEC HoldsRelease_ap and click Next. 6. This invokes a form that enables you to control the job. The current selection is Run Now, but instead you'll want to select Recurring Job. This enables the Schedule button, which you should press. 7. Select the options that are appropriate to your needs. In my case, the options were Run Daily, Every 10 Minutes. 8. The next page allows you to augment your job by sending e-mail or net send messages. In my case neither was relevant, but your needs may differ. SQL Server will now run the job every 10 minutes, changing the IsHold flag on the appropriate rows in the OnLineOrders table. The inventory calculations will then ignore the rows affected. If management decides to change the window length from say 10 minutes to 20 minutes, here's how you would adjust the job schedule in Enterprise Manager: 1. Expand the Management node. 2. Expand the SQL Server Agent node. 3. Select Jobs. 4. Right-click on the job of interest. 5. Select Properties. |