transactions

  • 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

  • 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/

  • Hi

    Tran count is set to 0

  • 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