November 19, 2009 at 12:17 pm
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
November 19, 2009 at 12:57 pm
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
November 19, 2009 at 2:12 pm
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.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 19, 2009 at 7:24 pm
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
November 20, 2009 at 6:47 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply