Suppress transaction in stored procedure

Question

Hi all,

I want to know wether or not it is possible to suppress a transaction inside a SQL stored procedure. I have the following situation in my SP (that I want to achieve):

WHILE TRUE
BEGIN TRY
    BEGIN TRANSACTION A
    RECEIVE MESSAGE FROM SSB QUEUE WITH TIMEOUT

    BEGIN SUPPRESS TRANSACTION 
      WHILE RECORD IN TABLE
         BEGIN TRANSACTION B
           DELETE RECORD FROM TABLE OUTPUT RECORD INTO D
           SEND RECORD D TO OTHER SSB QUEUE
         COMMIT TRANSACTION B
    END SUPPRESS TRANSACTION
    COMMIT TRANSACTION A
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION A
END CATCH

so what I really want to do is that transaction B does not get enlisted in transaction A :)

greetings, Tim

Answer 1

You're describing an "autonomous transaction", which is a common question from people migrating from Oracle (which supports them) to MSSQL (which doesn't). This article explains the various options, which unfortunately aren't particularly attractive:

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

If none of those options are practical for you, the other alternative is to shift some control into an application and out of the database, but of course that just shifts the issue to another location. Still, it may be worth considering.