I have a big problem with sql server(I use sql server version 2008 R2 with my application).
for example i have 3 table.
at the first my data save on Tmp_Table from Application then on Procedure Call from application
I have 3 Procedure aon database and from application call Main procedure.
Procedure PTwo() As Begin --Do Heavy Work insert into T_Dest1 Select * from Tmp_Table if @@ERROR > 0 Goto Rb; if @@ERROR > 0 Begin RB: Return (-1); End Else Begin Return (1); End End; Procedure PThree() As Begin --Do Heavy Work insert into T_Dest2 Select * from Tmp_Table if @@ERROR > 0 Goto Rb; if @@ERROR > 0 Begin RB: Return (-1); End Else Begin Return (1); End End; Procedure Main() As Begin Declare @Res_PTwo int, @Res_PThree int; Begin Tran T_Main; --Do Heavy work; Exec @Res_PTwo = PTwo(); if @Res_PTwo = -1 Goto RB; --Do Heavy Work; Exec @Res_PTHree = PThree(); if @Res_PThree = -1 Goto RB; Delete from Tmp_table if @@ERROR > 0 Begin RB: Rollback Tran T_Main; Return (-1); End Else Begin Commit Tran T_Main; Return (1); End; End; End;
for very long time and many customer all thing is good but in few time (for example 2 or three time in week or day)
Main Proc dont work correctly
and data from Tmp_Table copy to T_Dest1 correctly but don't copy in T_Dest2 but Main Proc return 1 that means
all thing is OK.
realy i confused and i don't know what is incorrect in my procedure and why my SQL Server dont work correctly.
is there any tools to find this mistake?
can i find when data to be lose from Tmp_Table?
thanks for all. and excuse me for my bad language.