存储过程中的事务错误使用SQL 2008 [英] Transaction Error in stored procedure Using SQL 2008

问题描述

在执行存储过程时出现此错误：



我执行的步骤
步骤：1）我创建了一个SP有一些临时表

2）从XML文件中获取数据以插入临时表并对Temp表执行更新和插入操作。

3）我写过

-----------------------

BEGIN TRY

BEGIN TRANSACTION

- 插入和更新操作

结束尝试

BEGIN CATCH

SET @ errorMsg = ERROR_MESSAGE（）

SET @ IsSucess = 0

ROLLBACK TRANSACTION

END CATCH



--- -----------------



此SP我正在使用SSIS包工作执行。



所以得到以下错误



错误： EXECUTE后的事务计数表示BEGIN和COMMIT语句的数量不匹配。以前的计数= 0，当前计数= 1



任何人都可以帮我这个。

解决方案

这意味着您可能正在尝试回滚不存在的事务或提交不存在的事务。你需要查看你的SQL，看看为什么会发生这种情况。

试试这样



BEGIN TRANSACTION

开始尝试

- 插入和更新操作

COMMIT TRANSACTION

结束尝试

BEGIN CATCH

SET @ errorMsg = ERROR_MESSAGE（）

SET @ IsSucess = 0

ROLLBACK TRANSACTION

END CATCH

Getting This error when I am executing Stored procedure:

Steps which i DID
Steps:1) I have created A SP having with some temp tables
2)Getting data from XML file to insert into temp tables and doing update and Insert operations into Temp tables.
3)I have written
-----------------------
BEGIN TRY
BEGIN TRANSACTION
--insert and Update operations
END TRY
BEGIN CATCH
SET @errorMsg= ERROR_MESSAGE()
SET @IsSucess=0
ROLLBACK TRANSACTION
END CATCH

--------------------

This SP I am Executing Using SSIS Package JOB.

so getting bellow error

Error : Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1

Can any one help me regarding this.

解决方案

It means you are perhaps trying to rollback a transaction that does not exist or commit one that does not exist. You need to go through your SQL and see why that is happening.

try like this

BEGIN TRANSACTION
BEGIN TRY
--insert and Update operations
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @errorMsg= ERROR_MESSAGE()
SET @IsSucess=0
ROLLBACK TRANSACTION
END CATCH

这篇关于存储过程中的事务错误使用SQL 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！