Re: help - transaction control

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 12:09:24 -0400

This message is informational only and does not affect the execution of the
batch. Take a look at 266 in BooksOnLine and see if that helps. 

-- 
Andrew J. Kelly  SQL MVP
"roger" <xrsr@rogerware.com> wrote in message
news:Xns9526556AF74FErsrrogerwarecom@204.127.199.17...
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in
> news:#D8$ujaaEHA.752@TK2MSFTNGP09.phx.gbl:
>
> > Roger,
> >
> > Yes transactions are nested but will always depend on the outer most
> > transaction to successfully commit before any of the inner ones are
> > committed.  If a Rollback is issued anywhere along the way ALL
> > transactions are immediately rolled back.  This is be design and is
> > how it has always worked.  So that is why it is imperative that before
> > you issue a Commit or Rollback you check the @@TRANCOUNT variable to
> > see if there is an open tran and avoid these errors.
> >
> > IF @@TRANCOUNT > 0
> >         COMMIT TRAN
> >
> > or
> >
> > IF @@TRANCOUNT > 0
> >     ROLLBACK TRAN
> >
> >
> >
>
> That doesn't affect what I'm seeing.
> I am calling rollback tran from inside my SP, which
> has begun a nested transaction, noting that @@trancount is 2
> before calling rollback tran, and that is when I get
> the error message that I mentioned.
>
>

Relevant Pages

  • Re: help - transaction control
    ... > transaction to successfully commit before any of the inner ones are ... > transactions are immediately rolled back. ... > ROLLBACK TRAN ... before calling rollback tran, and that is when I get ...
    (microsoft.public.sqlserver.programming)
  • Using SqlTransaction with MSSQL Trans?
    ... I got seven sp's that uses transactions: ... ROLLBACK TRAN ... will a commit in sp's be rollbacked by ado.net's rollback? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: does this backup sequence commit all data to the database
    ... the data was committed when the application transactions were ...  That could be an explicit COMMIT when an explicit BEGIN ... recovery mode there is no reason to backup the log. ... I understand that "backup log testdb to disk = '...'" will backup the ...
    (comp.databases.ms-sqlserver)
  • Re: commit_write= BATCH, NOWAIT; ... how long do I wait?
    ... we've started configuring the database servers with: ... minutes or so worth of transactions, so we're not deeply concerned by ... the durability loss associated with running in asynchronous commit ... Does anybody know how long Oracle will buffer redo in memory before it ...
    (comp.databases.oracle.server)
  • Re: Commit in SQL Task step
    ... Presuming you have enabled package transactions and chosen to join to it in ... a step but have not told anything when to commit then at then end of the ... package the steps are rolled back. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ...
    (microsoft.public.sqlserver.dts)