July 7, 2004 at 7:13 am
We have VB 6 apps running on SQL Server 7. Specifically, a purchase order program that among other things returns an incrementing integer as an order number when the user begins to enter a new order.
CREATE PROCEDURE procDGNextNumberRetrieve
@Type varchar(15),
@NextNum int OUTPUT
AS
SET NOCOUNT ON
Select @NextNum = NextNumber
from NextNumber
Where Type = @Type
Update NextNumber
Set NextNumber = @NextNum+1
Where Type = @Type
END
RETURN
On ocassion, we have found that two users will be assigned the same order number. We have been monitoring cpu and streamlining resource intensive sp's. Our suspicion is that when one of these long running sp's are executing, and bogging down the server, that in that delay, two users are being returned the same order number before the sp has time to finish, update and increment the previous number for the next user.
Was wondering if a locking hint would be useful in sovling this problem.
Thanks for your help.
July 7, 2004 at 5:51 pm
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply