This is my first post and apologies for the long message.

I'm having a nightmare trying to get a ServicedComponent
to rollback a transaction that covers the sending of
a message to a Message Queue (local or remote) AND the
deletion of a row from a table in SQL Server 2000.

Both operations must be guaranteed to either commit or
abort hence a I chose a ServicedComponent to do this.

The trouble is that no matter whether I make the Queue
transactional or non-transactional the sends to the
Queue are never rolled back. I'm forcing the attempted
rollback by renaming the stored proc so that the code can't
find the correct stored proc. The Send to the Queue is
performed before the SQL Server row deletion is
attempted. If the row deletion is performed first and I
attempt to send to a nonexistent Queue then the data
deletion is successfully rolled back so the problem is
occurring with the Queue.

The architecture of the System I am working on is
dependent upon being able to use Message Queues and use
them within distributed transactions.

I've now got down to the bare bones by including all the
code within one ServicedComponent (I originally
had a generic Message Queue Sender component, a separate
DALC for the data update and a middleman component
that initiated the transaction).

I've been through the articles on "Creating a Message
Queue Transaction with COM+ Services"
and "Reliable Messaging with MSMQ and .NET" and looked at
some of the posts relating to this (e.g. using
ContextUtil.SetAbort etc) but all to no avail.

The component I created was placed in the GAC using
GACUTIL (gacutil /i mysingletrans.dll) and I manually
registered the COM+ application using REGSVCS as a Library
component. The client is using the version
registered in the GAC (Local Copy = False). I am taking
advantage of the [AutoComplete()] attribute and
using Exceptions to cause the Abort.

I would be very grateful if someone out there could
provide assistance with this. I really do not want to have
to re-architect a System at this stage and I've spent days
trying to solve this. I must be missing something
blindingly obvious. Please help !

I have provided the ServicedComponent code and the test
client code below (connection strings etc ommitted).

ServicedComponent Code
======================

Imports System.EnterpriseServices
Imports System.Messaging
Imports System.Data
Imports System.Data.SqlClient
Imports System.Reflection

#Region "Required ServicedComponent attributes (mainly for
object construction)"

' WARNING !! The only way to get this to work was to give
it an absolute path
<Assembly: AssemblyKeyFileAttribute
("c:\dev\research\singletransactionalworkflow\mysingletrans
\mysingletrans.snk")>

<Assembly: ApplicationName
("TransactionTests.MySingleTrans")>

<Assembly: ApplicationActivation(ActivationOption.Library)

<Assembly: Description("Test component that sends to both
a message queue and updates a database")>

#End Region

Namespace TransactionTests.MySingleTrans

   <Transaction(TransactionOption.RequiresNew), _
   ConstructionEnabled(Default:="<details ommitted>",
Enabled:=True)> _
   Public Class SingleTransComponent : Inherits
System.EnterpriseServices.ServicedComponent

       Private m_connectionString As String

       Protected Overrides Sub Construct(ByVal s As
String)
           ' // Construct method is called next after
constructor.
           ' // The configured connection string is
supplied as the single argument.
           Me.m_connectionString = s
       End Sub

       <AutoComplete()> _
       Public Sub DoTransaction(ByVal leadID As Long, _
               ByVal productID As Long, _
               ByVal queueName As String, _
               ByVal messageObject As Object, _
               ByVal priority As
System.Messaging.MessagePriority, _
               ByVal useMQJournalling As Boolean, _
               ByVal useMsgJournalling As Boolean)

           Try
               ' Add to Queue
               SendMessageToQueue(queueName,
messageObject, MessagePriority.Normal, _
                       False, False)

               ' Remove Pending Lead from PendingLeads
table
               removePendingLead(leadID, productID)

           Catch ex As PendingLeadRemovalException
               Throw ex
           Catch ex As Exception
               Throw ex
           End Try

       End Sub

       <AutoComplete()> _
       Private Sub removePendingLead(ByVal leadID As
Long, ByVal productID As Long)

           Dim conn As New SqlConnection

           Try
               conn.ConnectionString = m_connectionString

               Dim cmd As SqlCommand = New SqlCommand
("usp_RemovePendingLead", conn)
               With cmd
                   .CommandType =
CommandType.StoredProcedure
                   .Parameters.Add(New SqlParameter
("@LeadID", leadID))
                   .Parameters.Add(New SqlParameter
("@ProductID", productID))
               End With

               conn.Open()
               cmd.ExecuteNonQuery()
           Catch ex As SqlException
               ' // throw new exception and propagate
SQLException
               Throw New PendingLeadRemovalException
("Exception occurred trying to delete lead from
PendingLeads table", ex)
           Finally
               conn.Close()
           End Try

       End Sub

       <AutoComplete()> _
       Private Sub SendMessageToQueue(ByVal queueName As
String, _
               ByVal messageObject As Object, _
               ByVal priority As
System.Messaging.MessagePriority, _
               ByVal useMQJournalling As Boolean, _
               ByVal useMsgJournalling As Boolean)

           Dim msgQ As MessageQueue    ' the MessageQueue
           Dim msg As New Message      ' the Message.
Using Complex method (as opposed to Simple)

           ' Get Type of the messageObject that has been
passed and specify it in the
           ' array of formatter Types.
           Dim formatterTypes As Type() = New Type()
{messageObject.GetType}

           Try
               msgQ = GetMessageQueue(queueName)

               ' // Set Journalling capability on the
MessageQueue. Will override default Queue setting.
               msgQ.UseJournalQueue = useMQJournalling

               With msg
                   .Formatter = New XmlMessageFormatter
(formatterTypes)
                   .Priority = priority
                   .Body = messageObject

                   ' // Set Journalling capability at the
individual Message level
                   .UseJournalQueue = useMsgJournalling
               End With

               If msgQ.Transactional Then

                   Dim trans As New
MessageQueueTransaction
                   Try
                       trans.Begin()
                       ' Send Message object.
                       msgQ.Send(msg, trans)
                       trans.Commit()
                   Catch ex As Exception
                       ' Abort the transaction.
                       trans.Abort()

                       Throw ex
                   Finally
                       trans = Nothing
                   End Try

               Else
                   ' Send Message object.
                   msgQ.Send(msg)
               End If

           Catch ex As Exception
               ' // Re-raise exception
               Throw ex
           Finally
               ' // <todo>Should we issue msgQ.Close ?
</todo>

               ' Clear object refs
               msg = Nothing
               msgQ = Nothing
           End Try

       End Sub

       Private Function GetMessageQueue(ByVal queueName
As String) As MessageQueue

           Dim msgQ As MessageQueue

           Try
               If Not MessageQueue.Exists(queueName) Then
                  '  User will require appropriate
permissions to create the Queue.
                   MessageQueue.Create(queueName)
               End If

               msgQ = New MessageQueue(queueName)
           Catch ex As Exception
               ' Re-raise 'wrapped' exception
               Throw New Exception("Error binding to
Queue", ex)
           End Try

           Return msgQ

       End Function

   End Class

   Public Class PendingLeadRemovalException : Inherits
System.Exception
       Sub New(ByVal msg As String)
           MyBase.New(msg)
       End Sub

       Sub New(ByVal msg As String, ByVal innerException
As System.Exception)
           MyBase.New(msg, innerException)
       End Sub
   End Class

End Namespace

Client Code
===========

' Note: PendingLeads is a component providing data
retrieval (not included)
' LeadPT.Lead is a simple class with four properties.
' It has been placed in the GAC and is used as the message
' object.

Imports TransactionTests.MySingleTrans

Imports System.Messaging

Public Class Form1
   Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

   Public Sub New()
       MyBase.New()

       'This call is required by the Windows Form
Designer.
       InitializeComponent()

       'Add any initialization after the
InitializeComponent() call

   End Sub

   'Form overrides dispose to clean up the component list.
   Protected Overloads Overrides Sub Dispose(ByVal
disposing As Boolean)
       If disposing Then
           If Not (components Is Nothing) Then
               components.Dispose()
           End If
       End If
       MyBase.Dispose(disposing)
   End Sub

   'Required by the Windows Form Designer
   Private components As System.ComponentModel.IContainer

   'NOTE: The following procedure is required by the
Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   Friend WithEvents Button1 As
System.Windows.Forms.Button
   <System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
       Me.Button1 = New System.Windows.Forms.Button
       Me.SuspendLayout()
       '
       'Button1
       '
       Me.Button1.Location = New System.Drawing.Point(88,
96)
       Me.Button1.Name = "Button1"
       Me.Button1.TabIndex = 0
       Me.Button1.Text = "Run Test"
       '
       'Form1
       '
       Me.AutoScaleBaseSize = New System.Drawing.Size(5,
13)
       Me.ClientSize = New System.Drawing.Size(292, 273)
       Me.Controls.Add(Me.Button1)
       Me.Name = "Form1"
       Me.Text = "Form1"
       Me.ResumeLayout(False)

   End Sub

#End Region

   Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Button1.Click

       Dim pendingLeadsDS As DataSet
       Dim objPendingLeads As New
PendingLeads.PendingLeads
       Dim objLead As New LeadPT.Lead
       Dim objLeadTransfer As New
TransactionTests.MySingleTrans.SingleTransComponent

       Try
           pendingLeadsDS =
objPendingLeads.getPendingLeads
           Dim datatable As DataTable =
pendingLeadsDS.Tables(0)
           For Each dr As DataRow In datatable.Rows
               ' Instantiate LeadTransfer Serviced
component. Pass it the data required.
               ' For our prototype it will pass the
DataRow but an eventual solution will
               ' pass an XML document.

               With objLead
                   .Applicant = "John Doe"
                   .Postcode = "SW5 6YP"
                   .LeadID = DirectCast(dr.Item
("LeadID"), Integer)
                   Select Case dr.Item("ProductID")
                       Case 1
                           .Product = "Mortgage"
                       Case 9
                           .Product = "Term Assurance"
                       Case Else
                           .Product = "Unknown"
                   End Select
               End With

               ' When LeadID and ProductID were cast to
Long there was an InvalidCastException
               objLeadTransfer.DoTransaction(DirectCast
(dr.Item("LeadID"), Integer), _
                       DirectCast(dr.Item("ProductID"),
Integer), _
                       ".\servicedleadqueue", _
                       objLead,
Messaging.MessagePriority.Normal, _
                       False, _
                       False)

               Console.WriteLine("Processed LeadID " &
CType(dr.Item("LeadID"), String))

           Next

           MessageBox.Show("Completed lead
transfers", "Job Completed")

       Catch ex As Exception
           MessageBox.Show(ex.ToString, "Exception
occurred")
       Finally
           ' // explicitly clear reference to
PendingLeads object
           pendingLeadsDS = Nothing
           objLead = Nothing
           objPendingLeads = Nothing
           objLeadTransfer = Nothing
       End Try

   End Sub
End Class

Regards
Julian Baden