Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What happened to that email?

This question:

Created script to send mails using sp_send_dbmail- working like a charm.
Now searching for a way to get result code of sent mail (like Success = Recipient got it,
Failure = Did not get regardless of the reason).
I mean SP return codes 0 (success) or 1 (failure) refer to correct mail Profile, not missing Recipient, etc.
Frankly not sure this is possible as it looks like outside Sql Server authority/responsibility?!

asked in this thread on SQLServerCentral prompted me to do some research into Database Mail.  The result of the research is that there is no way to get this information from SQL Server.

Basically the way Database Mail/sp_send_dbmail works is that the message is placed in a Service Broker queue (sp_send_dbmail returns success), the external Database Mail executable reads the queue and sends the message to the designated SMTP mail server.  If the mail server accepts the message then Database Mail is done and the status is set to sent.  So, if you have an incorrect email address or the receiving server refuses it, SQL Server has no way to know.  In order to find this out you would need to use a valid Reply To or From email address and monitor that mailbox.

Here’s the query I use for checking Database Mail:

SELECT
SEL.event_type,
SEL.log_date,
SEL.description,
SF.mailitem_id,
SF.recipients,
SF.copy_recipients,
SF.blind_copy_recipients,
SF.subject,
SF.body,
SF.sent_status,
SF.sent_date
FROM
msdb.dbo.sysmail_faileditems AS SF JOIN
msdb.dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id

Let me know if you have any better ways to find errors for Database Mail.

Comments

Posted by Alasdair Thomson on 17 September 2009

Interesting, I never thought to apply that. This is a requirement I wanted to get awhile ago, never did find a solution.

Posted by ALZDBA on 17 September 2009

Another thing to think about when you use dbmail is that you have to do some cleanup because it keeps track of every single mail that has been sent !

e.g.

[code]

/********** cleanup old messages *********/

Declare @sent_before datetime

Select @sent_before = dateadd(MM,-1,dateadd(dd,datediff(dd,0,getdate()),0))

print convert(varchar(26),@sent_before,121)

exec dbo.sysmail_delete_mailitems_sp   @sent_before = @sent_before

delete

from dbo.sysmail_event_log

where log_date < @sent_before;

[/code]

Posted by Jack Corbett on 17 September 2009

ALZDBA,

Great point.  You'd think MS would have provided an SP for that, but I can't find one.

Posted by MG on 17 September 2009

Actually MS privides a few SPs for mail maintenance but they have put the sp part of the name at the end of the procedure name:

sysmail_delete_mailitems_sp

sysmail_delete-log_sp

There are quite a few more and they all start with 'sysmail'

Posted by Jack Corbett on 17 September 2009

MG,

Obviously you are correct and I misread ALDBA's comment since he is using sysmail_delete_mailitems_sp.  I always get messed up with those procedures because they are in the dbo schema and I always look for them in the sys schema since they are new to SQL Server 2005 and I assume that is where they will be.

Posted by Derek Wharton on 17 September 2009

I use ALZDBA code as an agent job.  All of these are in BOL.

Posted by obautina on 22 September 2009

Good stuff to know, I've bookmarked it

Leave a Comment

Please register or log in to leave a comment.