SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


transactions


transactions

Author
Message
rinashc
rinashc
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 10
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
Mike01
Mike01
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10523 Visits: 2615
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/
rinashc
rinashc
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 10
Hi

Tran count is set to 0
Mike01
Mike01
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10523 Visits: 2615
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search