Msg 7391, Distributed Transactions (DTC) on SQL Server

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 setu

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.

StackExchange Database Administrators Q#30235, answer score: 7