EXECUTE 后的事务计数表示缺少 COMMIT 或 ROLLBACK TRANSACTION 语句 - SQL Server 2005
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2265629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing - SQL server 2005
提问by Paresh
I am getting the error from the application as following with SQL server 2005
我在 SQL Server 2005 中从应用程序中收到如下错误
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0"
“EXECUTE 后的事务计数表示缺少 COMMIT 或 ROLLBACK TRANSACTION 语句。先前计数 = 1，当前计数 = 0”
How can I find the stage where this error is raised?
如何找到引发此错误的阶段？
How can I find the missing transaction or the stored procedure where it is not committed or getting rolled back?
如何找到丢失的事务或未提交或未回滚的存储过程？
回答by GSerg
I don't think anything is missing. It's probably an inner stored procedure that gets called from inside a transaction (TRANCOUNT = 1), starts its own transaction (TRANCOUNT = 2) and then rolls it back. Well, it means to roll it back, but rollback affects all transactions and not only the innermost one, so the procedure screws up the execution flow.
我不认为缺少任何东西。它可能是从事务内部调用的内部存储过程 (TRANCOUNT = 1)，启动自己的事务 (TRANCOUNT = 2)，然后将其回滚。嗯，意思是回滚，但是回滚会影响所有事务，而不仅仅是最里面的事务，所以程序搞砸了执行流程。
A way to find the place depends on available tools/skills. A better way is to use SQL Profiler that shows all commands executed by an application against the server. Find out the outermost stored procedure and go through its code looking for any other procedure calls.
找到地点的方法取决于可用的工具/技能。更好的方法是使用 SQL Profiler 来显示应用程序针对服务器执行的所有命令。找出最外面的存储过程并检查其代码以查找任何其他过程调用。
回答by AakashM
The system function @@TRANCOUNTwill return how many transactions you are currently in. As part of your investigation, insert PRINT @@TRANCOUNTor SELECT @@TRANCOUNTstatements at appropriate places to see what is going wrong.
系统函数@@TRANCOUNT将返回您当前进行的交易数量。作为调查的一部分，在适当的位置插入PRINT @@TRANCOUNT或SELECT @@TRANCOUNT声明以查看出了什么问题。
回答by KM.
that usually means that you had nested transactions and there was a ROLLBACK. you don't really provide any information about the code running, stored procedures, dynamic SQL, etc. So this is just a guess, but I would do a search for "ROLLBACK" and add PRINTs or INSERTs INTO YourErrorLogTable after each one, make sure that the content is unique enough to determine what ROLLBACK was hit. Another thing you can try is to add some TRY - CATCH blocks where you include PRINTs or INTO YourErrorLogTable in the CATCH. If you provide more details about the code being called (nested procedures, are you using try-catch blocks, dynamic sql, linq, etc.), I could give you more specific advise on how to find the problem.
这通常意味着您有嵌套事务并且有回滚。您并没有真正提供有关代码运行、存储过程、动态 SQL 等的任何信息。所以这只是一个猜测，但我会搜索“ROLLBACK”并在每个之后添加 PRINT 或 INSERT INTO YourErrorLogTable，使确保内容的唯一性足以确定击中了什么 ROLLBACK。您可以尝试的另一件事是添加一些 TRY - CATCH 块，其中在 CATCH 中包含 PRINT 或 INTO YourErrorLogTable。如果您提供有关被调用代码的更多详细信息（嵌套过程，您是否使用 try-catch 块、动态 sql、linq 等），我可以就如何找到问题提供更具体的建议。
回答by Goran?e
Check if you have Return command before COMMIT TRANor ROLLBACK TRAN. This is usual error because Return command ends procedure and there is no chance to COMMITit.
检查您之前COMMIT TRAN或之前是否有 Return 命令ROLLBACK TRAN。这是通常的错误，因为 Return 命令结束程序并且没有机会COMMIT。
