Nested Transactions: COM+ and StoreProcedures


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Nested Transactions: COM+ and StoreProcedures

  1. #1
    Jonni Faiga Guest

    Nested Transactions: COM+ and StoreProcedures


    Hi,
    I have a COM+ Queued Component that executes a stored procedure. This means
    that a Transaction is started when the COM+ method is called, not when the
    the store procedure is called. Therefore any COMMIT/ROLLBACK transaction
    code within the store procedure would be considered a nested transaction.
    If the store procedure does a whole lot of things, and then it calls ROLLBACK,
    then it seems that the whole COM+ transaction is Aborted. However what if
    we just want the store procedure rolled back, and not the COM+ transaction?

    In otherwords in a nested transaction, how can you configure it so that ROLLBACK
    TRANSACTION will not ROLLBACK the outermost Transaction, only the innermost
    transaction?

    Thanks,
    Jonni Faiga
    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: Nested Transactions: COM+ and StoreProcedures

    The problem that you're facing is that COM+ enlists its transactions through
    the use of MSDTC - that is, it uses a distributed rather than a local
    transaction.

    Distributed transactions to not support nesting - the transaction that
    you're starting in your sproc with the BEGIN TRAN statment is effectively
    being ignored. The rollback will always rollback to the outer transaction
    because there's no such thing as a nested transaction in a distributed
    setting.

    I'm sorry to say that I don't really have any good work arounds to this
    issue.

    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com

    "Jonni Faiga" <jonni@bigfoot.com> wrote in message
    news:3a6e9035$1@news.devx.com...
    >
    > Hi,
    > I have a COM+ Queued Component that executes a stored procedure. This

    means
    > that a Transaction is started when the COM+ method is called, not when the
    > the store procedure is called. Therefore any COMMIT/ROLLBACK transaction
    > code within the store procedure would be considered a nested transaction.
    > If the store procedure does a whole lot of things, and then it calls

    ROLLBACK,
    > then it seems that the whole COM+ transaction is Aborted. However what if
    > we just want the store procedure rolled back, and not the COM+

    transaction?
    >
    > In otherwords in a nested transaction, how can you configure it so that

    ROLLBACK
    > TRANSACTION will not ROLLBACK the outermost Transaction, only the

    innermost
    > transaction?
    >
    > Thanks,
    > Jonni Faiga



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center