 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / February 2008Sql Stored Proc Transactions
Hi, If I have the following scenario: 1. Stored Proc (ProcA) is started. 2. ProcA starts a transaction. 3. ProcA calls ProcB. 4. ProcB is successful. 5. ProcA then fails somewhere else and 'rollback transaction' is executed. Will ProcB's work be rolled back too? Doogie, Yes. Nothing is really committed until the first transaction commits. - RLF > Hi, > [quoted text clipped - 8 lines] > > Will ProcB's work be rolled back too? > Doogie, Yes. Nothing is really committed until the first transaction > commits. - RLF [quoted text clipped - 13 lines] > > - Show quoted text - Normally I would think that is great, in this case it actually hinders me. My problem is that ProcA is having issues that we'd like to debug. We have a debug table that we can write data to that is done with ProcB. But if my calls to ProcB are going to get rolled back when/if ProcA fails, then I haven't gotten anywhere. Is there a solution around the transaction issue? Doogie, I totally understand your problem. Perhaps ProcB could, before returning, do a: SELECT * FROM DebugTable This would route the contents of DebugTable to your query window. Not as useful as a table, but I/O external to the database cannot be rolled back, so you would have the details on the screen as of that point. Perhaps somebody else has a better idea, but this should let you keep moving on the debugging. RLF On Feb 14, 8:15 am, "Russell Fields" <russellfie...@nomail.com> wrote: > Doogie, Yes. Nothing is really committed until the first transaction > commits. - RLF [quoted text clipped - 17 lines] > > - Show quoted text - Normally I would think that is great, in this case it actually hinders me. My problem is that ProcA is having issues that we'd like to debug. We have a debug table that we can write data to that is done with ProcB. But if my calls to ProcB are going to get rolled back when/if ProcA fails, then I haven't gotten anywhere. Is there a solution around the transaction issue? > Doogie, > [quoted text clipped - 44 lines] > > - Show quoted text - Generally that would be a good idea. In this case, it won't help us, because this is going to move into prod (the debug table exists on prod) and the error we are trying to see happens very rarely, so it wouldn't be one we can monitor visually, we need to write data somewhere so we can review when/if it happens. Doogie, >> SELECT * FROM DebugTable >> >> This would route the contents of DebugTable to your query window. Not as ... > Generally that would be a good idea. In this case, it won't help us, > because this is going to move into prod (the debug table exists on > prod) and the error we are trying to see happens very rarely, so it > wouldn't be one we can monitor visually, we need to write data > somewhere so we can review when/if it happens. You have to get outside the transaction, some how. What version of SQL Server are you running? At least 2000 I assume. If your ProcA reaches the point where it intends to rollback, you could add code like the following. DECLARE @Hold TABLE (Column1 INT, Column2 VARCHAR(100)...) INSERT INTO @Hold SELECT ... FROM TraceTable WHERE TraceDateTime > DATEADD(minute, -1, GETDATE()) ROLLBACK TRAN INSERT INTO TraceTable SELECT ... FROM @Hold RETURN The table variable is not affected by rollback, so you can stuff the data back in after the rollback is complete. RLF On Feb 14, 11:08 am, "Russell Fields" <russellfie...@nomail.com> wrote: > Doogie, > [quoted text clipped - 31 lines] > > RLF Using a temp table worked very, very well. Thank you so much for the suggestion! Doogie, Glad that it helped. Like just about everything I know, I saw someone else do it first. Question: When you said temp table (e.g. #TableName) worked for you, did you really mean table variable (e.g. @TableName) as my example showed? (I would not have expected a #TableName to work.) RLF On Feb 14, 11:08 am, "Russell Fields" <russellfie...@nomail.com> wrote: > Doogie, > [quoted text clipped - 33 lines] > > RLF Using a temp table worked very, very well. Thank you so much for the suggestion! > Doogie, > [quoted text clipped - 52 lines] > > - Show quoted text - Sorry, didn't come back to this post until now. I meant table variable, not temp table. If you dont open a tran then your logging will not be rolled back ------------- On Feb 14, 8:15 am, "Russell Fields" <russellfie...@nomail.com> wrote: > Doogie, Yes. Nothing is really committed until the first transaction > commits. - RLF [quoted text clipped - 17 lines] > > - Show quoted text - Normally I would think that is great, in this case it actually hinders me. My problem is that ProcA is having issues that we'd like to debug. We have a debug table that we can write data to that is done with ProcB. But if my calls to ProcB are going to get rolled back when/if ProcA fails, then I haven't gotten anywhere. Is there a solution around the transaction issue? > If you dont open a tran then your logging will not be rolled back > ------------- [quoted text clipped - 31 lines] > > - Show quoted text - Opening a transaction is required. The real (non-debug) work in the proc is updating/deleting from multiple tables and that all has to be wrapped in a transaction so that if anything fails, it all rolls back. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.