sp_send_dbmail question

  • I have a question regarding sp_send_dbmail. I've just used it for the first time and everything works well but after putting my solution into production I realized that one of the emails created was sent to an invalid address due to a configuration error.

    I have created a view to join my email tracking tables to msdb.dbo.sysmail_mailitems, and I am using the sent_status column to determine the mail status. I have translated the codes as 0=unsent, 1=sent, 3-retrying. I have determined these code values by reading some of the system views so they seemed correct at the time.

    My problem is that when an email was sent to an invalid email address the code value of 1/sent is returned, even though it never arrived at the destination.

    Does anybody know of a way to determine if there are delivery errors for sql generated emails ?

    Thanks,

    Bill, Charlotte NC

  • Short answer is that I don't believe SQL provides this functionality, just like it didn't in SQL Mail..

    Unfortunately the only *I* know how to take care of this requires some outside work. You need to make sure that the address used to send the email can get email back (ie it has a mailbox somewhere). Then you need to use a tool like the Bounce DLL from Chilkat Soft to actually read the email. You could probably build this without such a DLL but it requires more work..

    I would love to here if anyone else has a better solution..

    CEWII

  • Agree with Elliott, SQL does not have that functionality out of the box, and some additional work needs to be done for tracking bounced email.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • you can try to use logparser to dump the smtp log files into a database and query it. this will depend on the smtp server the email people use. We use MS Exchange internally and a third party server as the smtp gateway

  • All the above answers are correct. This is a failing in database mail in my opinion. I have a blog post about this issue here.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply