Very Computer

I have two stored procedures, one of which calls the other (see sample
below). When I provide data that I know will generate an error in add_a, I
get this:

Msg 266, Level 16, State 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is
missing. Previous count = 1, Current count = 0.
Msg 3903, Level 16, State 1
The rollback transaction request has no corresponding BEGIN TRANSACTION.

If I wrap the calling code in a begin/commit transaction, I get two of these
error messages. Anyone know why?

===================
Procedures sample
===================

create procedure add_a



as
begin transaction

    .... (do some sql stuff) ....

        rollback transaction

    end
commit transaction

and

create procedure add_b



as
begin transaction

        rollback transaction

    end
commit transaction

You cannot nest transactions like you are expecting.  The first rollback or
commit you execute in your example commits or rolls back the outermost
transaction, and from that point forward, there is no transaction in
progress.

Yep, it's an irritating facet of SQL Server (maybe ANSI SQL, but I'm not up
on that).

--
Rick Weaver
Promus Hotel Corporation

188510

Ron,

I think you will find an detailed answer with examples in:
"Microsoft SQL Server 6.5 unleashed, Second Edition, isbn 0-672-30956-4"
in Chapter 9, Transaction Management, Transactions and Stored procedures,
page 293-298

grig
--------------------------------------