Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing"

Question

Post reply

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing"

kuppurajm

SSCommitted

Points: 1593
More actions
August 22, 2011 at 2:57 am

#246617

Hi,
I got the error
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 9, current count = 10."
my sp is
-------------
ALTER PROC [dbo].[Usps_AuditDetails]
@InvoiceNum varchar(100)=null,
@InvoiceDate datetime=null,
@SupplierID int=null,
@CustomerID int=null,
@DocumentID int=null,
@FormatID int=null,
@status bit =null,
@createdby varchar(50)=null,
@CreatedOn datetime=null,
@XmlData xml=null
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
select @@TRANCOUNT
declare @AuditMasterID int
insert into Document_Audit_Summary(InvoiceNum,invoicedate,SupplierID,CustomerID,DocumentID,FormatID,
CreatedBy,Status)
values(@InvoiceNum,@invoicedate,@SupplierID,@CustomerID,@DocumentID,@FormatID,
@createdby,@Status)
select @AuditMasterID= MAX(AuditMasterID) from Document_Audit_Summary
insert into Document_Audit_Summary_Details(AuditMasterID,MaterialID,Quantity,Amount,
CreatedBy,Status)
SELECT
@AuditMasterID,
cast(Colx1.query('data(MaterialID) ') as varchar) as [sid],
cast(Colx.query('data(Quantity) ') as varchar) as [sname],
cast(Colx.query('data(Amount) ') as varchar) as [sname],
@createdby,1
FROM @XmlData.nodes('NewDataSet/tbl') AS Tabx(Colx)
COMMIT
END TRY
BEGIN CATCH
print(error_message())
IF @@TRANCOUNT > 0
ROLLBACK
select error_message()
END CATCH
END
-------------
and also after getting this error I can not open and I can not select this two tables.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Execute the rollback statement a few times until you get a message

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Jayanth Kurup[/url]

kuppurajm SSCommitted Points: 1593 More actions · Answer 2

hi,

Thank you.Now I can able to select the table.

but if there is any error in sp still the error comming again....

error:

--------

ransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

There's an interesting article on MSDN covering nested transactions:

http://msdn.microsoft.com/en-us/library/ms189336.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

Jayanth_Kurup (8/22/2011)
Execute the rollback statement a few times until you get a message
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Actually this will produce 2 errors. When you rollback a transaction it doesn’t matter in what nesting level you were while the transaction was rolled back. It will always close the transaction and set the transaction nesting level to zero. If you’ll run the rollback statement more then one time, then you’ll get 2 errors. The first error will be that you issued a rollback with no corresponding begin transaction. The second error will be the similar to the error that the original user got – “Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 9, current count = 0” (but current count will be zero instead of 10).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/