Click here to Skip to main content
15,917,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello dear all,
i have a store procedure sample given below , when SP is executed from application duplicate records are inserting and which is not all the time rarely , before this problem was not there in live, when my data base size increased and sql server getting struck(because of SSRS Reports consuming more resources ) in the middle operation ,due to the load duplicate records inserting in to DB. how to prvent or how to identify the root cause, some time for simple insert it is happening.
i user Begin trans and if exist . important note : in local while debugging no problem mainly this is in live that is also rarely but now frequently happening 10 records per the day are duplicate . please suggest any thing regarding this
SQL
ALTER PROCEDURE [dbo].[PR_Storeprocedure](
@parameters 1
@parammete 2..

)

AS
BEGIN

BEGIN TRY
-------------- BEGIN TRY -------------- 
SET NOCOUNT ON;  
	declare @paramter 3
	declare @paramter 4 to 30
	 
	DECLARE @TRAN_ID INT
        Declare @TRAN_BAL Table(TRN_ID int)


		while(len(@paramter 3)>0)
		begin
		

set @Param3=substring(condition)-- here i will get 1,2,4,3 values 
	
BEGIN TRANSACTION
	
     if not exists (condition @Param3 )
begin

if(@Attendence is not null)
	begin
	   if(@type='R')
		begin 
                 insert into table1 ( column 1 )
                 values(value 1)

                update dbo.table1  set cloum2=@prameter20 where condition
 ------------ACCOUNTS INTEGRATION-------------------------------
            
             INSERT INTO @TRAN_BAL EXEC [dbo].[PR_SP] @parameter=@param1 
             SELECT @TRAN_ID=MAX(TRN_ID) FROM @TRAN_BAL

             IF(@TRAN_ID=0 OR @TRAN_ID IS NULL)
              BEGIN
                RAISERROR ('NOT INSERTED IN ACCOUNTS',16,1)
                ROLLBACK TRANSACTION
                RETURN
              END
            ELSE
              BEGIN
               UPDATE dbo.Table2 SET TRAN_ID=@TRAN_ID ,MODIFIED_DATE=GETDATE()WHERE condition
              END
    ------------END-ACCOUNTS INTEGRATION---------
                 end
	  end
set @Param3=substring(condition)-- this will repeat the condition

	COMMIT TRANSACTION
	
	end
	
	
 END TRY
-------------- END TRY -------------- 
------------ CONDITION TO CHECK, EITHER TO "COMMIT" OR "ROLLBACK" TRANSACTION ------
	BEGIN CATCH
	
		RAISERROR('ERROR IN TRANSACTION',16,1)
	rollback TRANSACTION
		SELECT ERROR_NUMBER()	AS ERRORNUMBER
			,ERROR_SEVERITY()	AS ERRORSEVERITY
			,ERROR_STATE()		AS ERRORSTATE
			,ERROR_PROCEDURE()	AS ERRORPROCEDURE
			,ERROR_LINE()		AS ERRORLINE
			,ERROR_MESSAGE()	AS ERRORMESSAGE;

	
	END CATCH
end
Posted
Updated 7-Dec-12 2:58am
v2
Comments
RedDk 11-Feb-13 13:26pm    
This stuff is pseudocode ... I'd help if it was something on which I could do the "debugging" myself. Oh, and about the load issue. These duplicates likely always there and no ORDER BY is used so you can't see them (big database, undetectables, etc ...). Try DISTINCT in the SELECT?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900