Rollback whole transaction save/keep error message/error s..

Hi all,

In SQL 2005, is it possible to rollback the entire transaction but
save/keep the system generated error message/error severity
information some where? I tried to store the error message in local
memory but once rollback I lost it.

Any ideas would be highly appreciated.

Thanks.

On Feb 9, 8:38 am, "Bob" wrote:
> Hi all,
>
> In SQL 2005, is it possible to rollback the entire transaction but
> save/keep the system generated error message/error severity
> information some where? I tried to store the error message in local
> memory but once rollback I lost it.
>
> Any ideas would be highly appreciated.
>
> Thanks.

I don't understand what you mean when you write that you tried to
store the error information in memory but after rollback the
information is gone. If you rollback a transaction it does not
effect variables (including table variables) at all.

SQL Server 2005 introduced new error handling functions that we can
use in order to get information about run time errors that occurred
during the code's run time. Using the try catch block it is very easy
to rollback the transaction and then store the information about the
error in a logging table. I suggest that you'll have a look in BOL
for explanation and example of the new try catch blocks (it also
includes explanation and example of 6 new error handling functions
that give you plenty of information about the error).

Adi