Re: help - transaction control

From: roger (xrsr_at_rogerware.com)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 15:21:04 GMT

"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)
Loading