March 25, 2009 at 7:55 am
Hello,
I have a asp.net application hooked up to SQL Server 2005. The .net app calls a stored procedure on sql server which looks like this:
ALTER PROCEDURE [dbo].[p_SaveRepairStage]
@stageID bigint OUTPUT,
@repairID bigint,
@stageType tinyint,
@result tinyint,
@userid varchar(50),
@station varchar(50),
@endDate datetime=null,
@comments varchar(500),
@lastTestStageID bigint
AS
if (@stageID=-1)
begin
INSERT INTO SystemRepairStage
(
RepairID,
StageType,
Result,
UserID,
Station,
EndDate,
Comments,
LastTestStageID
)
VALUES
(
@repairID,
@stageType,
@result,
@station,
@endDate,
@comments,
@lastTestStageID
)
SET @stageID=IDENT_CURRENT('SystemRepairStage')
end
else
begin
UPDATE SystemRepairStage SET
Result=@result,
EndDate=@endDate,
Comments=@comments
WHERE StageID=@stageID and RepairID=@repairID and EndDate is null
end
Every now and then (and it is very sporadic) I get duplicated records that look like the example below.
Not always the same number of them (i was only expecting to insert 1)
Please note the StartDate (the column is defaulted to GetDate())
StageIDRepairIDStageTypeResultStartDateUserIDEndDateCommentsLastTestStageIDStation
1051324305259102009-03-24 10:31:29.720phanyv2009-03-24 13:51:02.400no videoNULLCR-TEST1
1051331305259102009-03-24 10:31:29.783phanyv2009-03-24 13:51:19.307no videoNULLCR-TEST1
1051332305259112009-03-24 10:31:29.783phanyvNULLNULLNULLCR-TEST1
Has anyone ever seen anything like this ? Am i doing something wrong here or is it sql server?
March 25, 2009 at 7:58 am
The only time I've seen that happen was when the .NET app was accidentally calling the proc multiple times. Either the "Submit" button (or its equivalent) was being clicked repeatedly, or there was something wrong with error-handling and it kept trying.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2009 at 8:21 am
That was my first thought as well but the start date (which is generated by sql server)
is too close (sometimes identical) for it to be a double click by the user.
Incidentally i have measures in place to prevent that sort of situation.
March 25, 2009 at 8:35 am
could there be a trigger on the destination table that is causing the issue?
Lowell
March 26, 2009 at 7:01 am
No. Not a single trigger on the entire database.
March 29, 2009 at 4:17 pm
Have you debugged the app to make sure it's not calling the stored proc twice?
March 29, 2009 at 6:32 pm
By the way, although I don't think that it is the cause of your current problem, you probably should be using SCOPE_IDENTITY() instead of IDENT_CURRENT().
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 7:16 am
Thanks for all the suggestions. I will convert all my stored procs to use scope_identity as i do think that may be part of my problem
It does not appear that the web app itself is at fault.
March 30, 2009 at 7:41 am
I agree that it could also be a situation where there could be a
trigger on that table causing a double insert.
However, you should also have a unique index (if possible) on
that table. That should prevent dupes and you can have the
procedure throw an exception back to the application if it
encounters that condition.
Also, I have had to add some javascript on the client side to
disable the save/submit/etc.. button, to prevent the user from
the IUS (Impatient User Syndrome). Particularly with web apps.
I couldn't tell from the data you provided, if a unique index could
be applied. Maybe you can add a compound index to generate a
unique entry from column data?
Hope this helps.
AL
March 30, 2009 at 8:16 am
I'm just asking because I'm puzzled:
You define parameter @StageID as OUTPUT but one of the very first things you do is test it --if (@stageID=-1)-- but at that point it is not initialized, I would think, so wouldn't that have unpredictable results?
March 30, 2009 at 8:24 am
@stageID is acually INPUTOUTPUT and is initialized in the web app to -1
I use to decide if i'm dealing with a "new" object (insert needed) or an existing one (update needed)
March 30, 2009 at 8:27 am
More generally, the OUTPUT parameter modifier does not prevent them being used for input also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 8:36 am
I should have thought of this before, but wasn't thinking clearly.
To prevent dupes also you could wrap your insert in "if not exists" t-sql statement with Begin/End.
ie:
if not exists ( select * from xxxx where stageid = @stageID and .... )
Begin
insert ...
End
I really don't know what you are doing with the -1 there.
Al
March 30, 2009 at 8:41 am
Thanks for the clarification on the OUTPUT parameter...
I'd be suspicious of your logic in the asp.net app which decides whether it is 'New' or not, and thus sets that parameter to -1.
Someone whos know SS better than I might comment, but why not let the stored proc decide? I.e., pass in the data and try an update. If @@Rowcount <> 1 then the record is not found (or something similar) and then do an insert.
What type of control are you using in ASP.NET (SQLDatasource, ObjectDataSource, inline code, maybe there's something there you can post here)?
March 30, 2009 at 8:43 am
You are correct i could use the not exist but why? Checking a variable for a value you know the column can never have is just as effective but quicker
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply