SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested Commit while rollback transaction


Nested Commit while rollback transaction

Author
Message
Eric Peterson
Eric Peterson
SSChasing Mays
SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)SSChasing Mays (646 reputation)

Group: General Forum Members
Points: 646 Visits: 65
I know that I have done this before, but cannot find my code.

I am debugging a complex set of stored procedures. These procs are executed within a transaction from an online application. The errors do not occur when running the procedure in my query editor.

What I am looking to do is write out a row to a progress tracking table, but my problem is that when the error occurs, everything gets rolled back including my trace. my output table would contain something like the following with datetime and spid information as well...

exec procedure 1
exec proc 2
exec proc 3
exec proc 4
exec proc 5

Anyone have the code that will allow me to commit these rows while leaving the outer transaction in place?

Thanks in advance
Eric Peterson
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54315 Visits: 9730
The only solution I can easily think of is write to a log file instead of a log table. Rollback won't "unwrite" the log file.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27784 Visits: 18995
Per Books Online:

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.


The rest is here: http://msdn2.microsoft.com/en-us/library/ms189336.aspx

So - it sounds like you're stuck unless you can set up the outer process outside of a "traditional transaction"....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Shashi-252355
Shashi-252355
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 74
You can use table variable for ur tracking data, u can insert the tracking data into it between the transactions and finally it wouldn't be removed after even roll back of the transaction. At the end, after the transaction, u can insert the data from ur table variable to ur physical tracking table.

hope it helps.
-Shashi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search