Click here to Skip to main content
15,898,732 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Dear Professionals


there is problem in my Proc that showing Exception-

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Here is my Proc---

SQL
if(@ProcID=2)
   begin

        if(@ExamID=3)
           begin
            begin transaction
              begin try

       insert into T_Marks_ProcesResult_triggerUpdate
      (
      R.Can_ID, R.Practical_S, R.Practical_W, R.Trade_S, R.Trade_W, R.Work_S, R.Work_W, R.Eng_S, R.Eng_W, R.Social_W, R.Cognate, R.Absent, R.Process_Date,
                      R.Result, R.Total, R.recFlag, R.General_English_S, R.General_English_W, R.General_Knowladge_S, R.General_Knowladge_W, R.Practical_Shorthand_S,
                      R.Practical_Shorthand_W, R.Practical_Typing_S, R.Practical_Typing_w, R.Trade_Theory_Shorthand_S, R.Trade_Theory_Shorthand_W, R.Trade_Theory_Typing_S,
                      R.Trade_Theory_Typing_W, R.Secretarial_Practice_S, R.Secretarial_Practice_W, R.G_Trade, R.G_Work, R.G_Eng, R.G_Social, R.G_General_English,
                      R.G_General_Knowladge, R.G_Trade_Theory_Shorthand, R.G_Trade_Theory_Typing, R.G_Secretarial_Practice, R.failCondition, R.MR,MProcessDate,Exam_id

      )
      Select  R.Can_ID, R.Practical_S, R.Practical_W, R.Trade_S, R.Trade_W, R.Work_S, R.Work_W, R.Eng_S, R.Eng_W, R.Social_W, R.Cognate, R.Absent, R.Process_Date,
                      R.Result, R.Total, R.recFlag, R.General_English_S, R.General_English_W, R.General_Knowladge_S, R.General_Knowladge_W, R.Practical_Shorthand_S,
                      R.Practical_Shorthand_W, R.Practical_Typing_S, R.Practical_Typing_w, R.Trade_Theory_Shorthand_S, R.Trade_Theory_Shorthand_W, R.Trade_Theory_Typing_S,
                      R.Trade_Theory_Typing_W, R.Secretarial_Practice_S, R.Secretarial_Practice_W, R.G_Trade, R.G_Work, R.G_Eng, R.G_Social, R.G_General_English,
                      R.G_General_Knowladge, R.G_Trade_Theory_Shorthand, R.G_Trade_Theory_Typing, R.G_Secretarial_Practice, R.failCondition, R.MR,getdate(),3 From
     (
       Select
        doc.col.value('SubjectID[1]', 'int') as SubjectID
       ,doc.col.value('Can_id[1]', 'int') as Can_id
       ,doc.col.value('Eng[1]', 'int') as Eng
       ,doc.col.value('Practical_S[1]', 'int') as Practical_S
       ,doc.col.value('Practical_W[1]', 'int') as Practical_W
       ,doc.col.value('Trade_S[1]', 'int') as Trade_S
       ,doc.col.value('Trade_W[1]', 'int') as Trade_W
       ,doc.col.value('Work_S[1]', 'int') as Work_S
       ,doc.col.value('Work_W[1]', 'int') as Work_W
       ,doc.col.value('Eng_S[1]', 'int') as Eng_S
       ,doc.col.value('Eng_W[1]', 'int') as Eng_W
       ,doc.col.value('Social_W[1]', 'int') as Social_W
       ,doc.col.value('Cognate[1]', 'int') as Cognate
       From @xml.nodes('Marks/ProcessTable') doc(col)
      ) as T
      inner join T_Marks_ProcesResult_SCVT as R on R.Can_ID=T.Can_id



     Update R set
      Practical_S=T.Practical_S,
      Practical_W=T.Practical_W,
      Trade_S=T.Trade_S,
      Trade_W=T.Trade_W,
      Work_S=T.Work_S,
      Work_W=T.Work_W,
      Eng_S=T.Eng_S,
      Eng_W=T.Eng_W,
      Social_W=T.Social_W ,Result=NUll,failCondition=null,Total=NUll ,Process_Date=GETDATE()
       , G_Trade=NUll,G_Eng=NULL,G_Work=NUll,G_Social=Null,MR=2
      From
     (
       Select
        doc.col.value('SubjectID[1]', 'int') as SubjectID
       ,doc.col.value('Can_id[1]', 'int') as Can_id
       ,doc.col.value('Eng[1]', 'int') as Eng
       ,doc.col.value('Practical_S[1]', 'int') as Practical_S
       ,doc.col.value('Practical_W[1]', 'int') as Practical_W
       ,doc.col.value('Trade_S[1]', 'int') as Trade_S
       ,doc.col.value('Trade_W[1]', 'int') as Trade_W
       ,doc.col.value('Work_S[1]', 'int') as Work_S
       ,doc.col.value('Work_W[1]', 'int') as Work_W
       ,doc.col.value('Eng_S[1]', 'int') as Eng_S
       ,doc.col.value('Eng_W[1]', 'int') as Eng_W
       ,doc.col.value('Social_W[1]', 'int') as Social_W
       ,doc.col.value('Cognate[1]', 'int') as Cognate
       From @xml.nodes('Marks/ProcessTable') doc(col)
      ) as T
      inner join T_Marks_ProcesResult_SCVT as R on R.Can_ID=T.Can_id

     ---------------------Update result
     EXEC usp_insert_ResultProcess_Online_SCVT 'U',@xml;
     EXEC usp_insert_ResultProcess_Online_SCVT 'SU',@xml;
     EXEC usp_insert_ResultProcess_Online_SCVT 'G',@xml;
     EXEC usp_insert_ResultProcess_Online_SCVT 'SEC_G',@xml;

     EXEC usp_insert_ResultProcess_Online_SCVT 'U',@xml;
     EXEC usp_insert_ResultProcess_Online_SCVT 'SU',@xml;
     EXEC usp_insert_ResultProcess_Online_SCVT 'V_G',@xml;

     Exec usp_subWise_Failpass_online_SCVT   @xml
     set @msg='Result Process Successfully!'

        commit transaction
        end try

   begin CATCH
   set @msg=ERROR_MESSAGE();
   rollback transaction
   END Catch

        end

   end


end




any suggestion...
Posted
Updated 24-Oct-14 20:06pm
v2
Comments
OriginalGriff 25-Oct-14 1:57am    
Without your code? Not much chance we can help really.
Use the "Improve question" widget to edit your question and provide better information.
Shubh Agrahari 25-Oct-14 2:07am    
hi, now i write my proc that is still giving exception....

1 solution

Obviously when you have an IF clause there will be branches that will not call the below code (i.e. when @examid is no 3):
SQL
if(@ExamID=3)
   begin
    begin transaction

hence there will be no transaction to rollback.
 
Share this answer
 
Comments
Shubh Agrahari 25-Oct-14 2:46am    
shitt..there is still problem....can you explain your answer.....that help to remove the error...
Mehdi Gholam 25-Oct-14 2:51am    
Remove the IF clause.
Shubh Agrahari 25-Oct-14 3:02am    
no its necessary to put that if clause.
Mehdi Gholam 25-Oct-14 3:07am    
Then your commit and rollback should also have one.
Shubh Agrahari 25-Oct-14 3:11am    
yes one thing that i focused after commenting the transaction block proc running proper..i think there is problem with nested proc that also executing in above block....what u say...??

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