asp.net - SQL Server Stored Procedure Creating Duplicates -
i running website using sql server 2008 , asp.net 4.0. trying trace issue down stored procedure creating duplicate entries same date. thought may couple post issue duplicates recording same date down milliseconds. 1 of duplicates @ :'2013-04-26 15:48:28.323' of data same except id.
@check_date
input stored procedure gives particular date looking @ (entries maid daily)
@formheaderid
grabbed earlier in stored procedure, getting header id detail table 1 many relationship header.
the @getdate()
entry found duplicate entries, there entries exact getdate()
values different rows.
this doesn't occur each entry either, randomly occurring in application.
select @formheaderid=stage2_checklist_header_id stage2_checklist_header environmental_forms_id=@envformid , checklist_monthyear=@inspected_month order start_date desc if @formheaderid = 0 begin insert stage2_checklist_header( environmental_forms_id ,start_date ,checklist_monthyear ,st2h_load_date ) values( @envformid ,@check_date ,@inspected_month ,getdate()) set @formheaderid = scope_identity() print 'inserted new header record ' + cast(@formheaderid varchar(50)) end if (not exists( select * stage2_checklist_detail stage2_checklist_header_id = @formheaderid , check_date = @check_date )) insert stage2_checklist_detail (stage2_checklist_header_id, check_date, st2_chk_det_load_date, inspected_by) values (@formheaderid, @check_date, getdate(), @inspected_by) set @form_detail_id = scope_identity() print 'inserted detail record ' + cast(@form_detail_id varchar(50))
here similar case developer able track duplicate entries simultaneous calls different spids (which sidestepped exists check). after experimenting isolation levels , transactions - , trying avoid deadlocks - sounds solution in case use sp_getapplock , sp_releaseapplock.
Comments
Post a Comment