|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Do not rollback sql stmts executed before RAISE ERROR_________________________________________________________ <proc 1> ----sql stmts---- if <condition1> begin exec <another stored proc>proc2 raise error ('some msg', 16,1) return end ----rest of stored proc 1---- ___________________________________________________________ My problem is that when condition1 is met it raises the error but the effect of proc2 is rolled back. How do I get proc2 to execute and commit and then raise the error ? Is there any way NOT to rollback sql stmts executed before the RAISE ERROR statement ? Thanks in advance, Barq Try this...
<proc 1> ----sql stmts---- if <condition1> begin rollback transaction begin transaction exec <another stored proc>proc2 commit transaction raise error ('some msg', 16,1) return end Show quote "barq" <zeitgeis***@lycos.com> wrote in message news:1157562549.431544.58120@p79g2000cwp.googlegroups.com... > I've a stored proc which has a raise error: > _________________________________________________________ > <proc 1> > > ----sql stmts---- > > if <condition1> > begin > > exec <another stored proc>proc2 > raise error ('some msg', 16,1) > return > > end > > ----rest of stored proc 1---- > > ___________________________________________________________ > > My problem is that when condition1 is met it raises the error but the > effect of proc2 is rolled back. > How do I get proc2 to execute and commit and then raise the error ? Is > there any way NOT to rollback sql stmts executed before the RAISE ERROR > statement ? > > Thanks in advance, > > Barq > Thanks Jim but that doesn't work either.
The problem seems to be that the raise error stmt rolls back all stmts executed before it even when they are committed with an explicit commit tran stmt. Barq Jim Underwood wrote: Show quote > Try this... > > <proc 1> > > ----sql stmts---- > > if <condition1> > begin > rollback transaction > begin transaction > exec <another stored proc>proc2 > commit transaction > raise error ('some msg', 16,1) > return > > end > "barq" <zeitgeis***@lycos.com> wrote in message > news:1157562549.431544.58120@p79g2000cwp.googlegroups.com... > > I've a stored proc which has a raise error: > > _________________________________________________________ > > <proc 1> > > > > ----sql stmts---- > > > > if <condition1> > > begin > > > > exec <another stored proc>proc2 > > raise error ('some msg', 16,1) > > return > > > > end > > > > ----rest of stored proc 1---- > > > > ___________________________________________________________ > > > > My problem is that when condition1 is met it raises the error but the > > effect of proc2 is rolled back. > > How do I get proc2 to execute and commit and then raise the error ? Is > > there any way NOT to rollback sql stmts executed before the RAISE ERROR > > statement ? > > > > Thanks in advance, > > > > Barq > > > Thanks Jim but that doesn't work either. This should not happen unless you stripped the "ROLLBACK TRANSACTION"> The problem seems to be that the raise error stmt rolls back all stmts > executed before it > even when they are committed with an explicit commit tran stmt. which Jim used. If you do not use "ROLLBACK TRANSACTION", then the subsequent "BEGIN TRANSACTION" may in fact not start a new transaction if the whole block was allready in transaction (it only increments transaction counter), in which case the "raise error" could cause the whole transaction to be rolled back. If however "ROLLBACK TRANSACTION" is issued, then the outside transaction should be rolled back, and the subsequent code (BEGIN TRAN; execute proc2; COMMIT) should get commited properly and not get rolled back. This solution may not be used in a case when "proc2" should "see" the changes that "proc1" made. Kamil 'Hilarion' Nowicki Look up SAVEPOINT in Books Online.
Please post more of the relevant parts of the procedure. It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 ) The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "barq" <zeitgeis***@lycos.com> wrote in message news:1157562549.431544.58120@p79g2000cwp.googlegroups.com... > I've a stored proc which has a raise error: > _________________________________________________________ > <proc 1> > > ----sql stmts---- > > if <condition1> > begin > > exec <another stored proc>proc2 > raise error ('some msg', 16,1) > return > > end > > ----rest of stored proc 1---- > > ___________________________________________________________ > > My problem is that when condition1 is met it raises the error but the > effect of proc2 is rolled back. > How do I get proc2 to execute and commit and then raise the error ? Is > there any way NOT to rollback sql stmts executed before the RAISE ERROR > statement ? > > Thanks in advance, > > Barq > barq (zeitgeis***@lycos.com) writes:
Show quote > I've a stored proc which has a raise error: RAISERROR does not cause any rollback, not even when SET XACT_ABORT ON> _________________________________________________________ ><proc 1> > > ----sql stmts---- > > if <condition1> > begin > > exec <another stored proc>proc2 > raise error ('some msg', 16,1) > return > > end > > ----rest of stored proc 1---- > > ___________________________________________________________ > > My problem is that when condition1 is met it raises the error but the > effect of proc2 is rolled back. > How do I get proc2 to execute and commit and then raise the error ? Is > there any way NOT to rollback sql stmts executed before the RAISE ERROR > statement ? is in force. So it seems that you are not telling us the full story. For a better answer you better give us more details. Which version of SQL Server are you using? Are you using TRY-CATCH? In which context is the stored procedure called? From an application? From Query Analyzer? Something else? The actual code would also help. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Sorry everybody - I was not clear about the problem.
I took a second look at my problem - SQL server DOES NOT rollback for raiseerror - the proc was being called by another application and it was that application that was rolling back - I missed that the first time. So everything's o.k. now. Sorry for the confusion and any irritation I might've caused by the incomplete question. Thanks for all the replies. Barq Erland Sommarskog wrote: Show quote > barq (zeitgeis***@lycos.com) writes: > > I've a stored proc which has a raise error: > > _________________________________________________________ > ><proc 1> > > > > ----sql stmts---- > > > > if <condition1> > > begin > > > > exec <another stored proc>proc2 > > raise error ('some msg', 16,1) > > return > > > > end > > > > ----rest of stored proc 1---- > > > > ___________________________________________________________ > > > > My problem is that when condition1 is met it raises the error but the > > effect of proc2 is rolled back. > > How do I get proc2 to execute and commit and then raise the error ? Is > > there any way NOT to rollback sql stmts executed before the RAISE ERROR > > statement ? > > RAISERROR does not cause any rollback, not even when SET XACT_ABORT ON > is in force. > > So it seems that you are not telling us the full story. For a better > answer you better give us more details. Which version of SQL Server > are you using? Are you using TRY-CATCH? In which context is the stored > procedure called? From an application? From Query Analyzer? Something > else? The actual code would also help. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics