CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
hcanes hcanes
hcanes hcanes

Reputation: 19

Msg 3902, Level 16, State 1. The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

DECLARE @cnt_inv int,
        @cnt_jrn int, 
        @pl_per varchar(2), 
        @pl_yr  varchar(4), 
        @pl_jrn varchar (6), 
        @pl_inv varchar (6)

IF @@ERROR <> 0
BEGIN
BEGIN TRANSACTION JD_Imp


            IF @cnt_inv > 0 
            BEGIN
            BEGIN TRANSACTION JD_Inv



        COMMIT TRANSACTION JD_Inv; 
                    PRINT N'The Invoice Commits DONE.';
        END

            IF @cnt_jrn > 0 
            BEGIN
            BEGIN TRANSACTION JD_Jrn


        COMMIT TRANSACTION JD_Jrn;  
                    PRINT N'The Journals Commits DONE.';
        END
COMMIT TRANSACTION JD_Imp;
END

Upvotes: 0

Views: 6245

Answers (2)

Adam Robinson
Adam Robinson

Reputation: 185703

The core of your issue is this:

IF @cnt_jrn > 0 
        BEGIN TRANSACTION JD_Jrn

All this will do is only start a new transaction if @cnt_jrn > 0. It's still going to execute all of the code below regardless of the condition. So if @cnt_jrn <= 0, it's going to call commit transaction JD_Jrn without ever having started it.

You need to enclose the body of any multi-statement if body with begin and end. For example:

IF @cnt_jrn > 0 
BEGIN
        BEGIN TRANSACTION JD_Jrn

        ... code ...
END

But you are enclosing single insert and update statements in transactions, which is not necessary. SQL operations are guaranteed to be atomic, so you only need a transaction if you're spanning multiple operations.

Upvotes: 2

hcanes hcanes
hcanes hcanes

Reputation: 19

Problem solved.....

(a) Having the Begin...END Blocks did solve the error message Msg 3902.. I noticed that without the BEGIN..END Blocks, previous runs of the procedure will still be hanging uncommitted

(b) IF @@ERROR <> 0 BEGIN will always be true so no wonder the script block inside was not being executed.

(c) The Debugger was not moving past the IF statement due to (b).

Thanks a lot Adam.

Upvotes: 0

Related Questions