If you nest transaction you are stuck with this, but have you considered
using savepoints (SAVE TRANSACTION)?

Kresimir Radosevic,SQL MVP

"Jonas W." <jonas_wollingerhotmail.com> wrote in message
news:db7a28a.0307012326.7a9fac45posting.google.co m...
> Is there any way to perform the following by just using stored
> procedures in SQL Server 2000 (I know this doesn't work, both updates
> are rolled back):
> BEGIN TRANS T1
> UPDATE TABLE1 SET MyColumn = 'ABC'
> BEGIN TRANS T2
> UPDATE TABLE2 SET MyColumn = '123'
> COMMIT TRANS T2
> ROLBACK TRANS T1
>
> The desired result is that the update on TABLE1 shall have been rolled
> back while the update on TABLE2 shall have been committed.