Re: Transactin and Rollback.

The only other possibility I can think of is that something in your
code is committing the original transaction and starting a new
transaction after the first table is changed and before the child table
is changed. Rollback should rollback all uncommitted changes within the
current transaction. It is pretty hard to believe that transactions do
not work in SQL server so the answer must be somewhere else.

--
Bill Todd (TeamB)
.

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... enclosing transaction or should begin and commit/rollback its own ... > I echo Ron's points and also have been using an approach very similar to> Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the> ChildSP is called from the ParentSP. ... > The ParentSp now has to ROLLBACK everything up to that point. ... As Ron> mentioned, the ParentSP may have called many child SPs, and performed many> updates up to this point, and all of these need to be rolled back. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... This will take care of what is done in the child, but what I want to do is ... rollback the entire outer transaction - the one initiated in the outer SP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)