Tony Rogerson's ramblings on SQL Server

While writing some notification stuff around the conflict detection in merge replication I got a problem - the sp_send_mail I was executing was not sending out the email even though I know email was working; basically in the merge stored procedure conflict resolved, if you decide to choose the subscriber as the winner and the subscriber has removed the row then for some reason the agent rolls back the stored procedure transaction (enough on that, I shall leave merge conflicts for another day).

Back to the task in hand; the SQL below will only send Test 1 and Test 3 messages even though you get the mail queued message.

So, becareful in those maint routines - you may never get the email if it is sent within a transaction and that transaction is rolled back; its a good and bad thing - mostly good I guess because it means the sending of emails really is transactionally aware - this is probably down to database mail essentially being a service broker queue.

An example in real life is if you've used this in a trigger to notify you of an RI problem or an event and you are rolling back the trigger then you won't get the email.

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'REPLICATION',

    @recipients = 'tonyrogerson@torver.net',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Test 1' ;

begin tran

exec msdb.dbo.sp_send_dbmail

    @profile_name = 'REPLICATION',

    @recipients = 'tonyrogerson@torver.net',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Test 2' ;

rollback

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'REPLICATION',

    @recipients = 'tonyrogerson@torver.net',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Test 3' ;  

If I have time over the weekend I'll write up the work round; basically its to make sure the email is sent outside of the transaction - that means calling an external process (CLR procedure for instance).