February 23, 2009 at 3:14 pm
I have a simple INSERT into one table. So far 5,347 rows have been inserted. The problem is that 51 of them are duplicates. With the exception of the Identity column the duplicate rows are exactly the same in every column including two which are GetDate() DATETIME columns.
The stored procedure is called from a .NET program data layer using the Microsoft Data Application Block.
If every INSERT was duplicated the problem would be easy to find. But when it occurs less than 1% of the time I really don't know how to approach the problem. The stored procedure is:
ALTER PROCEDURE [dbo].[dnECPFormInsert]
@PackageMasterID int,
@MasterDocumentID int,
@user-id int
AS
SET NOCOUNT ON
BEGIN TRANSACTION TRANS_INS
BEGIN
INSERT INTO TBLECPPACKAGESUB
(
PackageMasterID,
MasterDocumentID,
ApprovedFormDate,
ApprovalFormUserID,
LastModifiedUserID,
LastModifiedDateTime,
Deleted
)
VALUES
(
@PackageMasterID,
@MasterDocumentID,
GETDATE(),
GETDATE(),
0
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION TRANS_INS
RETURN
END
END
COMMIT TRANSACTION TRANS_INS
Any ideas?
February 23, 2009 at 3:30 pm
Do you have code in your .NET code to disable the submit button on click? Could it just be someone double-clicking the button/resubmitting whatever they're doing?
Your stored proc makes no attempt to check for duplicates - sounds like it should.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 3:45 pm
You could put a Key on the table (Primary Key, Alternate Key/Unique Index) to prevent this. That is what they are for.
[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]
February 23, 2009 at 3:53 pm
I'd recommend doing what Matt AND RBarry recommended, if you can.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 23, 2009 at 6:43 pm
Yeah, it sounds like duplicate entries and you don't have a natural key defined on the database. That is one of the problems with using an Identity only PK method. Go and read some of Joe Celko's Jeremiad's on the topic. Like Barry says, you need another constraint in addition to the PK to prevent duplication of data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2009 at 7:52 pm
"Jeremiad"? Cool word! I learn something new every day. And hear I was reading from Jeremiah just yesterday (Jeremiah is definitely the ranter's ranter).
[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]
February 25, 2009 at 6:25 am
Thanks everyone,
Since it has taken about a year and one-half to total 5,300 records and the table is narrow we've added the unique index. This should prevent the problem at relatively low cost.
Thanks again.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply