Wow! you folks have been busy! I'm just reading this at 2:pm EDT and by time I got to the end, I forgot what the original purpose was. I had to go back and re-read some sections to catch up.
We have a mail-order business which also employs a call center and Internet sales. We have from 10-25 people (depending on season) taking orders by phone or from mail and the web runs 24-7. Each entry person uses the same custom OrderEntry program (VB6) which is the front end, SQL '08 is the DB. Each order gets a unique order number. As you can see by the sample of the OrderNumber table below (sorry for alignment), the TYPE of order can determine the ordernumber. Still, if there were only 1 type, it wouldn't matter. Simply put, the calling procedure adds a 1 to the current ordernumber. The next person gets the current ordernumber and adds a 1, etc. etc. the web system pulls from the same table. NOte the column "next availableOrderNumber". In my 2.5 years here, we've never had a collision or duplication and this system has been running since the late 90's. The calling program identifies the type based on location and feeds that as a parameter to the calling sproc. It's just a glorified counter. Sorry if I'm missing the point.
CatalogYearOrderTypeRangeDescriptionNextAvailableOrderNumberOrderNumberRangeLowOrderNumberRangeHigh
2012C1Credit Card Orders 0400000499999
2012C2Credit Card Orders w/Cards 0570000579999
2012C3Credit Card Orders (FAX) 0890000895999
2012C4Credit Card Orders (EMail) 0580000589999
2012C5Credit Card Orders (999999) 0870000873999
2012C6Credit Card Orders (WEB Site) 0580000599999
2012E1Employee Charge 0899000899999
2012R1Regular (Check) Orders 0600000799999
2012R2Regular (Check) Orders 0855000857999
2012R3Regular(Check) Orders (999999) 0855000857999
2012T1Telephone Orders 100219100000129999
2012W1WalkIn Charge 0897500897999
2012X1WalkIn Check 0897000897499
2012Z1Employee Check 0898000898999
Ajit, I'm more curious about how you scheduled your diagnostic procedure to run and find the blocks. I've copied and run it, but it found nothing, because I don't think anything was actually going on at the time. Did you just run it once, or did you have it repeat periodically, knowing the approximate time the offending procedure would run?
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."