July 16, 2010 at 5:07 am
System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Hi Guys
I have a stored Procedure that calls another stored procedure.
Both stored procs have Begin Trans and rollback trans if an error occurs
the nested stored procedure updates and modifies data but the main stored procedure does not modify data except for calling the nested stored proc.
Is it necesassy for the main proc to have a being tran and rollback tran when that is being taken care in the nested proc. Could this be throwing the error above. @@Error 266
Eg code
Create Procedure One as
Begin Tran
exec two
If @@Error <> 0
Begin
Raiserror ("Error thrown in proc two," 16,-1);
Rollback Tran
End
Commit
Create Procedure two as
Begin Tran
Update some tables
If @@Error <> 0
Begin
Raiserror("Error updating",16,-1)
Rollback
End
Commit
exec one
July 16, 2010 at 6:35 am
you should check @@Transcount to determine if a transaction is opend or not. See this article on MSDN regarding nested transactions.
http://msdn.microsoft.com/en-us/library/ms189336.aspx
For better, quicker answers, click on the following...
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/
July 16, 2010 at 6:49 am
Hi
Tran count is set to 0
July 16, 2010 at 7:12 am
You aren't checking for @@Transcount in your code. Also, you should use a try catch block and return after the error.
Create Procedure One as
declare @ErrorMessage varchar(300)
Begin Tran
begin try
exec two
end try
begin catch
if @@Transcount > 0
Rollback Tran
select @ErrorMessage = Error_Messate()
Raiserror (@ErrorMessage, 16,-1);
return
end catch
if @@Transcount > 0
Commit tran
go
Create Procedure two as
declare @ErrorMessage varchar(300)
Begin Tran
begin try
Update some tables
end try
Begin catch
if @@Transcount > 0
Rollback Tran
select @ErrorMessage = Error_Messate()
Raiserror (@ErrorMessage, 16,-1);
return
End catch
if @@Transcount > 0
Commit tran
go
For better, quicker answers, click on the following...
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply