SQL中的问题是 - ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION [英] problem in SQL that is -- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
本文介绍了SQL中的问题是 - ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的专业人士
我的Proc中有问题显示异常 -
ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION
这是我的Proc-- -
if (@ ProcID = 2)
开始
如果(@ ExamID = 3)
开始
开始 交易
开始尝试
插入 进入 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
)
选择 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_Engli sh_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
(
< span class =code-keyword>选择
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(< span class =code-string>' Eng_W [1]',' < span class =code-string> 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_ProcesRes ult_SCVT as R on R.Can_ID = T.Can_id
更新 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
来自
(
选择
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(' < span class =code-string> 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 加入跨度> 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 = ' 结果处理成功!'
提交 交易
end 尝试
开始 CATCH
set @ msg =错误信息();
回滚 交易
END Catch
end
end
end
任何建议......
解决方案
显然当你有一个IF
子句会有分支不会调用下面的代码(即当@examid为3时):
if (@ ExamID = 3)
begin
begin 交易
因此没有要回滚的交易。
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
---------------------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...
解决方案
Obviously when you have anIF
clause there will be branches that will not call the below code (i.e. when @examid is no 3):
if(@ExamID=3) begin begin transaction
hence there will be no transaction to rollback.
这篇关于SQL中的问题是 - ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文