Exclude from transaction

Question

Exclude from transaction

fzurbuchen

SSChasing Mays

Points: 631
More actions
September 18, 2003 at 11:51 pm

#81118

Is there a way to exclude a t-sql statement from a transaction?
I have a stored procedure called from another programm.
The calling programm starts a transaction and roll it back if an error occurs.
My stored procedure consists of update statements and some audit code.
When the transaction is rolled back, my audit code is also rolled back.
Can I exclude my audit code from the transaction?
placed this topic in the administrator forum which mey be the wrong place

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

adiseshan Valued Member Points: 73 More actions · Answer 1

Hi

you can close your transaction after updates and then have ur audit. I believe there will be some conditions on which you will enter ur audit code.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

Do transactions persist across databases?

If not then you might be able to store your audit details in a separate table.

If the do then I think you are stuck.

Jeremy

fzurbuchen SSChasing Mays Points: 631 More actions · Answer 3

How can I adjust if a transactions persist across databases or not?

Felix

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 4

could you just put the audit info into audit table when a rollback occurs, effectively auditing the rollback ?

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 5

The question was not about adjusting the transaction but if you write something to table in a separate database whether that get rolls back if your transaction in the current database rolls back.

I don't know the answer. Perhaps someone out there knows.

Jeremy

Edited by - Jeremy Kemp on 09/19/2003 05:01:48 AM

fzurbuchen SSChasing Mays Points: 631 More actions · Answer 6

ripg1011

the transaction is not under my control,

its the caller who starts and commit/rollback the transaction.

Felix

paulhumphris Hall of Fame Points: 3496 More actions · Answer 7

I think you will find that the ROLLBACK will also rollback anything you have written to your audit tables.

In line with the way that many other applications work, you should perhaps consider writing your audit information out to a text file (i.e. in the same way that SQL Log files provide an audit trail of errors raised).

Ɖiamond ǤeezeƦ SSC Eights! Points: 905 More actions · Answer 8

From reading this page in BOL it would seem like transactions persist across databases.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80

Perhaps the Distributed Transaction Manager can be configured to exclude certain tables?

Keith Grimsey BSc MBCS CEng

Senior Software Engineer

Keith

Ɖiamond ǤeezeƦ SSC Eights! Points: 905 More actions · Answer 9

The link didn't seem to come out correctly in my last post. Search for the topic 'Distributed Transactions Architecture' in BOL.

Keith Grimsey BSc MBCS CEng

Senior Software Engineer

Keith

paulhumphris Hall of Fame Points: 3496 More actions · Answer 10

Asking SQL to exclude certain Tables from the Transaction is unlikely to be possible.

Transaction integrity is one of the core aspect of a databases ACID compliance, it is very unlikely that there is any way to bypass this fundimental functionality.

Edited by - paulhumphris on 09/19/2003 05:25:43 AM

fzurbuchen SSChasing Mays Points: 631 More actions · Answer 11

writing audit info to a file seems to be the way to go...

thanks for all the brainpower so far

Felix