1

I'm using SQL Server's 

sp_send_dbmail

to distribute mail. Everything is working fine; BUT I tried to test a bad email address and it always reports success; and nothing is in the failed sent items table.

Will this procedure report IF the email isn't sent? Or is success just getting the mail to the exchange server?

Thanks in advance!

|improve this question
  • 1
    Take a look at the sysmail_allitems table (msdn.microsoft.com/en-us/library/ms175056.aspx). What is the sent_status for that test email to the bad email address? – Thomas Stringer May 28 '13 at 14:18
  • Both my valid and invalid test email addresses report a sent_status of 1. I think sql server knows it reached (or was sent to) the mail server, but doesn't know if it got sent. – fjr_nj May 28 '13 at 14:28
  • I just tested in sql 2008 to a bad address and the sent_status shows up as failed for me – Cougar9000 May 28 '13 at 14:31
  • bad To: email address? hmmm, what mail server are you using? I'm using Exchange with a "service account" and Anonymous authentication. – fjr_nj May 28 '13 at 14:40
  • Would you mind sharing your config settings? – fjr_nj May 28 '13 at 15:26
4

The procedure will not report if the email is not received. It will be upon you and your support team to troubleshoot why the email was not received. SQL Server just cares about being able to execute the call. Thomas Stringer pointed out sysmail_allitems in a comment and that will show failed status:

USE msdb;
select recipients, sent_status
FROM sysmail_allitems
GO

If it actually failed the sent_status field will show failed

Edit: Found some more stuff. sp_send_dbmail does not provide a function for "feedback" on the success or failure of the email. You can check the status using the sysmail_xxx tables and in the database mail log. The best I have been able to do on an AD exchange server relaying through anonymous SMTP is a failed status in sysmail_allitems and an error msg in the Database mail log stating unknown user

Links Jack Corbett

|improve this answer
  • Thank you. Unfortunately, my sysmail_allitems never show failure...if I could get that feedback, it would be better than nothing. – fjr_nj May 28 '13 at 17:55
0

You can't verify if the message has been delivered, or even if the address is correct. The procedure doesn't "send" a message, it just places it in Database Mail Queue. Notice that the result of sp is actually "Mail queued.", not "Mail sent" :).

The result/status of sending queued messages can be viewed here, in [description] column:

SELECT top 100 * FROM msdb..sysmail_log

You'd probably have to join the log to mail items:

SELECT top 100 * FROM msdb..sysmail_mailitems
|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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