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 = 'email@example.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 ('firstname.lastname@example.org','email@example.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.