August 22, 2011 at 2:57 am
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],
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.
August 22, 2011 at 3:06 am
August 22, 2011 at 3:19 am
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.
August 22, 2011 at 3:30 am
There's an interesting article on MSDN covering nested transactions:
http://msdn.microsoft.com/en-us/library/ms189336.aspx
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
August 22, 2011 at 4:21 am
Jayanth_Kurup (8/22/2011)
Execute the rollback statement a few times until you get a messageThe 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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply