Dear Professionals
there is problem in my Proc that showing Exception-
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Here is my Proc---
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
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...