Tony, You've probably already sorted this but, thought I'd wade in anyway.
There's a couple of things you can look at. The first I would suggest is that table variables do not participate in transactions, so you can insert into these within your transaction, rollback and then insert into your logging table proper. Other than that you can create savepoints in stored procs for partial rollback within an active transaction. Neil. On 26 March 2010 18:36, Tony Wright <[email protected]> wrote: > Actually the real issue is how to handle stored procedure failures and > logging of those failures. > > Here's the scenario: > In Linq to SQL, on calling SubmitChanges > (1) Linq to SQL starts a transaction > (2) Executes my stored procedure to perform a row insert on my object. We'll > use Brands for our object. BrandName is not allowed to have duplicates. > (3) If there are no problems with the insert, record is inserted, control is > returned to Linq to SQL, which Commits it's transaction. > (4) If there are duplicates in the BrandName, we want to abort the insert, > insert a record into the error log table, and then return the error back to > the client. > > The problem with this is that if we rollback the entire transaction, then > the error being logged will also be rolled back. So the entire outer > transaction needs to be successful (so the outer transaction cannot be > rolled back and the message is logged) even though the insert itself was a > failure. > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] > On Behalf Of Arjang Assadi > Sent: Friday, 26 March 2010 6:04 PM > To: ozDotNet > Subject: Re: Sql Server Nested Transactions > > Assuming if that was possible, but would it be a good idea? I mean if > they are nested transactions what would be the point of making them > act contradictory to the very idea of transaction? Either they all > succeed or they all fail, other wise they are separate transactions. > > If the intent is to have the inner transaction to roll back > independently from the out one, > then would it be more clear to do so without nesting it? > > For the sake of clarity they should be separated. > > Regards > > Arjang > > On 26 March 2010 17:42, <[email protected]> wrote: >> Hi all, >> >> I have a stored procedure with nested transactions. >> >> A rollback transaction occurs in the inner transaction, but I don't want > that to cause the outer >> transaction to rollback. >> >> Is that possible? It seems that the rollback always rolls back both > transactions. >> >> Regards, >> Tony >> >> > > > -- http://neildavidyoung.wordpress.com/
