[DOTNET] Can't Open Sql Connection With A Serviced Component When DB Is Remote

[Stephen Fulcher]

I have a piece of code that wraps an MSMQ send and a db insert into a single dist tx 
using a serviced component. It works fine when the db is on the box where the code 
runs. Otherwise, it fails to open the connection. I created a console app test that 
first does each operation separately, outside of the DTC, then tries to do both within 
the scope of a dist tx. The separate operations work, then the code barfs on the 
SqlConnection.Open() invocation inside the DTC transaction. The error occurs in the 
remoting layer. I nust have some piece left unconfigured for the remote db use over a 
dist tx. Has anyone else seen this?
 
Here's the code:

Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.EnterpriseServices
Imports System.Messaging

Namespace Test

  Module TestMod

    Public Sub Main()

      Try

        'try separate outside of DTC
        Dim Q As MessageQueue = New MessageQueue()
        Q.Path = ".\private$\dcnetmail"
        Q.Send("Hello", "No DTC", MessageQueueTransactionType.Single)
 Console.WriteLine("sent sep message...")

        Dim conn As SqlConnection = New SqlConnection("server=10.35.72.105;Integrated 
Security=true;database=dcnet")
 Console.WriteLine("created sep conn...")

        conn.Open()
        Console.WriteLine("opened sep conn...")
        Dim cmd As SqlCommand = New SqlCommand("insert into emailbatchlog 
(ebl_subject, ebl_message, ebl_from) values ('Hello', 'Test Sep Message', 
'[EMAIL PROTECTED]')", conn)
        Console.WriteLine("created sep cmd...")
        cmd.ExecuteNonQuery()
        Console.WriteLine("executed sep...")

        Dim obj as New EmailSender
        Console.WriteLine("created svcd obj...")
        obj.Send()
        Console.WriteLine("finished svcd dist tx with no errors - committed...")

      Catch e As Exception

        Console.WriteLine(e.ToString())

      End Try

    End Sub

  End Module

  <Transaction(TransactionOption.Required)> _
  Public Class EmailSender
    Inherits ServicedComponent

    Private Q As MessageQueue

    Sub New()

        Q = New MessageQueue()
        Q.Path = ".\private$\dcnetmail"

    End Sub

    <AutoComplete()> _
    Public Sub Send()

        Q.Send("Hello", "Test DTC Message", MessageQueueTransactionType.Automatic)
        Console.WriteLine("sent svcd message...")

        Dim conn As SqlConnection = New SqlConnection("server=10.35.72.105;Integrated 
Security=true;database=dcnet")
 Console.WriteLine("created svcd conn...")

        conn.Open()
        Console.WriteLine("opened svcd conn...")
        Dim cmd As SqlCommand = New SqlCommand("insert into emailbatchlog 
(ebl_subject, ebl_message, ebl_from) values ('Hello', 'Test Svcd Message', 
'[EMAIL PROTECTED]')", conn)
        Console.WriteLine("created svcd cmd...")
        cmd.ExecuteNonQuery()
        Console.WriteLine("executed svcd...")

    End Sub

  End Class

End Namespace

 

Here's the build:
c:\winnt\microsoft.net\framework\v1.0.3705\vbc /keyfile:tx.snk /debug:full 
/r:c:\winnt\microsoft.net\framework\v1.0.3705\system.dll 
/r:c:\winnt\microsoft.net\framework\v1.0.3705\system.enterpriseservices.dll 
/r:c:\winnt\microsoft.net\framework\v1.0.3705\system.data.dll 
/r:c:\winnt\microsoft.net\framework\v1.0.3705\system.messaging.dll testdisttx.vb
 
c:\winnt\microsoft.net\framework\v1.0.3705\regsvcs testdisttx.exe

 
Here's the output:
C:\DistTxTest>testdisttx
sent sep message...
created sep conn...
opened sep conn...
created sep cmd...
executed sep...
created svcd obj...
sent svcd message...
created svcd conn...
System.Runtime.InteropServices.COMException (0x8004D00E): Exception from HRESULT
: 0x8004D00E.
Server stack trace:
   at System.Data.SqlClient.SqlConnection.Open()
   at Test.EmailSender.Send() in C:\DCNET Drop\DistTxTest\testdisttx.vb:line 69
   at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean
fExecuteInContext)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMes
sage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage req
Msg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgDa
ta, Int32 type)
   at Test.EmailSender.Send() in C:\DCNET Drop\DistTxTest\testdisttx.vb:line 60
   at Test.TestMod.Main() in C:\DCNET Drop\DistTxTest\testdisttx.vb:line 34
b‹œjzÞiÙž²Æ zÇë¢kax3“4DÚ­Èb½ë§²æìr¸›yúè˜3“4Dè®Ë›±Êâmëh¢Ø^¬7¯zZ)1éí¢¹b²Û¶m§ÿÝŠÇ.²Ç^½éh¥Ê&