Thanks Neil, I've come up with a solution that involved overriding the onError routine in the Linq to Sql Domain Service. However, you've given me a couple of options to think about.
Regards, Tony On Wed, Mar 31st, 2010 at 10:36 PM, Neil Young <[email protected]> wrote: > 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/ > > >
