November 9, 2009 at 5:44 pm
I have a requirement where I have one main stored procedure that calls several nested stored procedures - that in turn make some table updates - and when one of those stored procedures fails the changes that all the previous stored procedures had made need to be rolled back. The bind is that only the main stored procedure can be changed and the nested ones can not. If the nested stored procedures run successfully they do commit their changes. How can I program into the main stored procedure to: 1) Recognize when the stored procedure failed 2) Rollback all changes that the previous stored procedures have made?
Thanks
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
November 10, 2009 at 1:07 am
If the nested stored procedures run successfully they do commit their changes.
Exactly what do you mean here?
Do the nested stored procedures have their own transaction handling i.e. BEGIN TRAN... IF OK COMMIT ELSE ROLLBACK
If they do then you have a problem. When COMMIT statements are "Nested", the changes aren't actually committed until the outermost COMMIT is done. But, a ROLLBACK at any point rolls back ALL the changes made so far, unless you are using savepoints.
November 10, 2009 at 2:16 am
Thanks Ten Century,
First day on a new Application System Analyst position today and here is the question posed to me by my new Manager:
"I have to kick off a stored procedure that has a transation in it, so i just want to make sure if I commit a transaction within a transaction it will still rollback that transaction and rollback all it."
The real world situation is that the main stored procedure calls a number of nested stored procedures that are SOX compliant. These SOX sp's can not be modified - from what they tell me, and I get the feeling they were specifically designed to ensure that we are in compliance with the stardards. Who wrote them and how we acquired them I haven't inquired about. The main sp from what I gather runs a bunch of job steps that all need to succeed or all be rolled back if one fails. Apparently there is periodic failures and my manager has been completing a backup before running and then if failures occur doing a restore. Wish I could tell you more but I'm blind at this point - no access to anything on the network - to be able to verify anything.
From what I have learned these nested (SOX) procedures wouldn't be committed to the database untill a commit statement is issued subsequently from the main script/procedure.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply