Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
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 7-Dec-12 2:25am
Edited 7-Dec-12 2:58am
28.9K
v2
Comments
RedDK at 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)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 383
1 OriginalGriff 304
2 George Jonsson 258
3 Animesh Datta 130
4 Shemeemsha RA 128
0 OriginalGriff 6,099
1 Sergey Alexandrovich Kryukov 5,411
2 CPallini 4,770
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100