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 > >
