Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Have you googled the error message –  swasheck Dec 12 '12 at 1:59
    
@swasheck My heavens YES! lol, I would never ask here without having done due diligence first. Even Pinal says this particular message can arise from many different causes. :) –  Chiramisu Dec 12 '12 at 2:01
2  
@Chiramisu You're googling for the wrong part of the error message. Try "The partner transaction manager has disabled its support for remote/network transactions." or "linked server was unable to begin a distributed transaction". –  Mark Storey-Smith Dec 12 '12 at 2:13
    
@MarkStorey-Smith I've actually Google'd several different parts of the message, but the particular issue I was having seemed too convoluted and I was having trouble finding an answer that applied to me. I think your answer might help though. I'll definitely give that a try when I get back to the office and let you know how it goes. Thanks. :) –  Chiramisu Dec 12 '12 at 7:28
add comment

1 Answer

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.

share|improve this answer
    
Unfortunately my infrastructure team shut me down on this for now because they are too overloaded, but hopefully I will have a chance to come back to it in one month. If not, I will assume this is a correct answer based on the positive ratings from other users. At any rate, thanks so much for your answer. :) –  Chiramisu Dec 12 '12 at 22:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.