question on failure inside a transaction

I want to issue a transaction where whole transaction should roll back
properly. I want to be sure that anything that stops these statements
from working will rollback the transaction. Example, the table2 no
longer exists below so I get error
Invalid object name 'table2'.
How do I get sql to gracefully rollback.. It seems to ignore my if
@.@.trancount > 0 rollback statement.
begin tran
insert into table1 (x,y,z) values(1,2,3)
insert into table2 (x,y,z) values(1,2,3)
if @.@.trancount > 0 rollback
commit tranAny severe error will stop the batch and any further code will not be run.
You might be able to use SET XACT_ABORT to do what you want or you can
handle it where you made the call in the first place.You might want to have
a look at this:
http://www.sommarskog.se/error-handling-II.html
Andrew J. Kelly SQL MVP
"David" <daveygf@.yahoo.com> wrote in message
news:1113009248.856813.214350@.o13g2000cwo.googlegroups.com...
>I want to issue a transaction where whole transaction should roll back
> properly. I want to be sure that anything that stops these statements
> from working will rollback the transaction. Example, the table2 no
> longer exists below so I get error
> Invalid object name 'table2'.
> How do I get sql to gracefully rollback.. It seems to ignore my if
> @.@.trancount > 0 rollback statement.
> begin tran
> insert into table1 (x,y,z) values(1,2,3)
> insert into table2 (x,y,z) values(1,2,3)
> if @.@.trancount > 0 rollback
> commit tran
>