Logging information in a transaction that does rollback.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ben brugman - 18 Mar 2008 11:21 GMT

We have a stored procedure and would like to register some information even
if the stored procedure is rolled back.

Current workings:
1. Client program start a transaction.
2. Client program call stored procedure.
3. Client program checks (simple check) if the stored procedure has done the
requested action.
4. Client program commits or does a roll back if the action is not finished
satisfactory.

If the client program doe doe a roll there is now no trace left in the
database about the transaction. (With a succesfull action the datetime, and
parameters are stored in a logging table).
Now we would like to leave a trace in the database with the essential
information about this aborted transaction and that without changing the
client program. Is this possible ? And if, how?

Reason, now we get the essential information indirectly from the client
program, but it would be more convenient if this information would still be
in the database.

ben brugman

Reply to this Message

Tibor Karaszi - 18 Mar 2008 14:34 GMT

The cleanest method I know of is to store whatever you need to log in a table variable and use that
information after your ROLLBACK. Obvious scope repercussions, of course.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> We have a stored procedure and would like to register some information even if the stored
> procedure is rolled back.
[quoted text clipped - 15 lines]
>
> ben brugman

Reply to this Message

ben brugman - 18 Mar 2008 17:27 GMT

> The cleanest method I know of is to store whatever you need to log in a
> table variable and use that information after your ROLLBACK. Obvious scope
> repercussions, of course.

There is no control after the Rollback, until the next call and because of
the rollback the process stops. So in that case I have to wait for a manual
restart and wait for the next call.

Isn't there a way to write for example into another db (e.g. tempdb) and
keep that 'alive' even when there is a rollback ?

Ben

P.s. I thought about the client program to do two calls, one to register and
the second to execute. But that means an overhaul of the client software, at
the moment my company is not prepared to alter this part of the software at
this moment.

>> We have a stored procedure and would like to register some information
>> even if the stored procedure is rolled back.
[quoted text clipped - 19 lines]
>>
>> ben brugman

Reply to this Message

Linchi Shea - 18 Mar 2008 15:22 GMT

Another method is to raise an informative error to write it to errorlog. That
can't be rolled back. You probably don't want to write a huge amount of data
to your errorlog, and that info is not really accessible programatically.
Oracle has the so-called autonomous transaction which can be used for this
purpose. SQL Server doesn't support it.

Linchi

> We have a stored procedure and would like to register some information even
> if the stored procedure is rolled back.
[quoted text clipped - 19 lines]
>
> ben brugman

Reply to this Message

ben brugman - 18 Mar 2008 17:37 GMT

Both Tibor and Linchi,

Thanks for your anwsers, now I know I do not have to search for a function
that does not exist.
Probably was thinking of the autonomous (Oracle) function, because somewhere
in my memory was something that the requested function did exist. Should
have used a database instead of my memory.

Thanks,
ben brugman

> Another method is to raise an informative error to write it to errorlog.
> That
[quoted text clipped - 34 lines]
>>
>> ben brugman

Reply to this Message