DELETE not removing all applicable rows

[evaleah]

I have the following stored procedure. Each time I run it, rows are deleted, but not all of them. If I run it a 3 times or so, then all the applicable rows are eventually deleted. The total number of rows needing deletion is only about 3500. Can anyone tell me what I missing? Is there a limitation I do not know about?

ALTER PROCEDURE xjr_DeleteCompleteStandardElementCrosswalk
(
	@SSet varchar(50),
	@SVersion varchar(50),
	@DSet varchar(50),
	@DVersion varchar(50)
)
AS
BEGIN
	declare @SSetID int
	declare @DSetID int
	
	SELECT @SSetID = pkStandardSetVersionRowID 
	FROM tblStandardSetVersions
	INNER JOIN tblStandardSets on tblStandardSetVersions.fkStandardSetRowID = tblStandardSets.pkStandardSetRowID
	INNER JOIN tblVersions on tblStandardSetVersions.fkVersionRowID = tblVersions.pkVersionRowID
	WHERE tblStandardSets.pakStandardSetRowID = @SSet AND tblVersions.pakVersionRowID = @SVersion
	
	SELECT @DSetID = pkStandardSetVersionRowID 
	FROM tblStandardSetVersions
	INNER JOIN tblStandardSets on tblStandardSetVersions.fkStandardSetRowID = tblStandardSets.pkStandardSetRowID
	INNER JOIN tblVersions on tblStandardSetVersions.fkVersionRowID = tblVersions.pkVersionRowID
	WHERE tblStandardSets.pakStandardSetRowID = @DSet AND tblVersions.pakVersionRowID = @DVersion
	
	DELETE tSEC FROM tblStandardElementCrosswalk tSEC
	LEFT JOIN xjrvw_StandardSetVersions vSSV on tSEC.fkStandardElementRowID = vSSV.pkStandardElementRowID
	LEFT JOIN xjrvw_StandardSetVersions vDSV on tSEC.fkRelatedStandardElementRowID = vDSV.pkStandardElementRowID
	WHERE 
		(vSSV.pkStandardSetVersionRowID = @SSetID AND vDSV.pkStandardSetVersionRowID = @DSetID)
		OR
		(vSSV.pkStandardSetVersionRowID = @DSetID AND vDSV.pkStandardSetVersionRowID = @SSetID)
END

Thanks!

Eva

 

[markros]

Check answers to you thread in MSDN forum

http://social.msdn.microsoft.com/Fo...l/thread/ceb8dad1-8210-4d09-81ea-e03111f96e32

PluralSight Learning Library

 

[sparkbyte]

This was resolved on the MSDN posting.

-------------------------------------------------------------

It turns out the lack of delete was not a lack at all. It was caused by the process still running in the background from a started ASP.NET app that I had killed the debugging on. So each time I killed the debugging I assumed .NET had killed the process as well. It had not. When I ran the delete script, it deleted what was THERE but could not delete rows that weren't there YET!

Thank you all for looking at this!



Thanks

John Fuhrman

 

[markros]

I still suggest to take also a look at the suggested approaches as there may be another potential problem.

PluralSight Learning Library