Msg 7391, Distributed Transactions (DTC) on SQL Server

Question

This may be DBA related or developer related, I honestly don't know.

I have two Database servers:

A: SQL Server 2005 (Trigger that uses DBMail on Server B)
B: SQL Server 2008 R2 (DBMail configured)

I have a table in Server A where email notifications are queued and I created an "after insert" trigger to execute sp_send_dbmail using Server B like so...

USE SampleDB
ALTER TRIGGER [dbo].[mail_inserted] 
   ON [dbo].[tblEmailQueue]
   AFTER INSERT
AS 
BEGIN
    declare @MailTo nvarchar(max), @MailSubject nvarchar(max), @MailBody nvarchar(max)
    -- Initialize vars
    select @MailTo=MailTo from inserted
    select @MailSubject=MailSubject from inserted
    select @MailBody=MailBody from inserted

    -- Send email via DbMail on ServerB
    exec [ServerB].msdb.dbo.sp_send_dbmail --this runs fine by itself, but not via trigger
        @profile_name = 'Test Profile'
        ,@from_address = 'noreply@test.com'
        ,@recipients = @MailTo
        ,@subject = @MailSubject
        ,@body = @MailBody
END

Here's where the weirdness begins that I need help with, mind you this seems to be a very poorly documented (or severely lacking thereof) feature.

Now I can execute that last statement with some sample data and everything works just fine. But when I try to insert into the table like so...

insert dbo.tblEmailQueue (MailTo,MailFrom,MailSubject,MailBody) values
    ('to-me@test.com','from-somebody@test.com','Subject','Body')

I get the following ERROR...

OLE DB provider "SQLNCLI" for linked server "ServerB"
    returned message "The partner transaction manager has disabled its support for
    remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure mail_inserted, Line 15
The operation could not be performed because OLE DB provider "SQLNCLI" for
    linked server "ServerB" was unable to begin a distributed transaction.

Perhaps I'm not understanding how Server A is connecting to Server B under the context of the trigger? Do I need to setup a special Login or something? I'm stumped.

Answer 1

I would imagine its either:

EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

Use this option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. When this option is TRUE (or ON) calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.

Or could be an issue with MSDTC configuration, which is a laborious and lengthy checklist to run through, documented fully in Recommended MSDTC settings for using Distributed Transactions in SQL Server.