February 9, 2011 at 10:53 am
I recently set up Database Mail in SQL Server 2005 running as a job. We typically send about 2000 emails a day.
sp_send_dbmail *always* returns zero as @@ERROR, but we've confirmed that sometimes the email never reaches its destination. I've concluded that sp_send_dbmail simply does not indicate a failure to connect to the email server. Which is pretty amazing since sending an email is its job.
Previously, using an internal email server and CDO, emails went out with no failures ever. When we switched to Gmail, CDO would occasionally return an error that seemed to indicate a transport error, and the email would not be sent. These failures would come in clusters during the day. I concluded the Gmail server sometimes gets busy and refuses to accept an email until it's ready.
Can anyone confirm that sp_send_dbmail does not indicate transport errors with @@ERROR? I found a list of error codes it supposedly returns, and they all pertained to the format of the email itself and so on; none indicated a failure to reach the email server.
February 9, 2011 at 11:45 am
dzoll (2/9/2011)
I recently set up Database Mail in SQL Server 2005 running as a job. We typically send about 2000 emails a day.sp_send_dbmail *always* returns zero as @@ERROR, but we've confirmed that sometimes the email never reaches its destination. I've concluded that sp_send_dbmail simply does not indicate a failure to connect to the email server. Which is pretty amazing since sending an email is its job.
Previously, using an internal email server and CDO, emails went out with no failures ever. When we switched to Gmail, CDO would occasionally return an error that seemed to indicate a transport error, and the email would not be sent. These failures would come in clusters during the day. I concluded the Gmail server sometimes gets busy and refuses to accept an email until it's ready.
Can anyone confirm that sp_send_dbmail does not indicate transport errors with @@ERROR? I found a list of error codes it supposedly returns, and they all pertained to the format of the email itself and so on; none indicated a failure to reach the email server.
1. Database mail doesn't report when the message is sent is it is setup even close to correct. The reason is that queueing of mail and transport of that mail to the recipient are seperated and database mail uses service broker to do it.
2. Previously using xp_sendmail or most other tools the email was sent at the moment you requested it, this is no longer true.
3. You can review this link for how to monitor the log: http://msdn.microsoft.com/en-us/library/ms191278.aspx
CEWII
February 9, 2011 at 12:09 pm
Thanks, Elliott. Sounds like a pain.
I have my own queue I'm pulling emails from, so I'm going to switch back to CDO within my stored procedure job and do my own retrying when it returns a transport error.
February 9, 2011 at 12:29 pm
Honestly I think that is a bad idea. Database mail is the preferred and supported mail technology moving forward. In all but the rarest cases could I recommend NOT using it.
I'm guessing you went to a queue because you wanted to be able to reliably send email and SQL Mail was anything but. I have done some similar things in the past because I didn't want a mail failure to kill a process. When database mail became available I switched to using it and my former process was tweaked to simply report on the database mail queue. This worked very well, also database mail can be setup for automatic retries, which I use. This should resolve or at LEAST mitigate the GMail busy issue you raised.
I would modify your process to monitor the database mail queue and not use other tech for support and simplification reasons.
CEWII
February 9, 2011 at 12:34 pm
Hmm. I had a look at the tables mentioned in the article you suggested. Looks like they would do the job except for one thing: How do you query for the status of the email you've just sent? I see internal ID numbers in the table but nothing to connect a record to the email Database Mail has just sent. Querying on the email address sent to or the subject is not specific enough.
February 9, 2011 at 12:46 pm
With the retries I mentioned I brought my failure count down to near zero and most of those were people who no longer worked for us. Exchange won't accept messages for addresses it KNOWS are invalid, for outside addresses its harder.
However, if you look at the sp_send_dbmail sproc definition you will see @mailitem_id is an OUTPUT parameter, this should get you what you need. you just need to store it when you send the email this will tie them together.
You also need a process (often manual) to handle email bounces, we used a specific mail box for this.
CEWII
February 9, 2011 at 12:59 pm
Thanks, and sorry to bother you a second time. I'll read up on @mailitem_id.
February 9, 2011 at 1:48 pm
Not a problem. I hope it all works out for you. Database Mail for me is very nice to work with. I met the gent who wrote it at PDC 05, shook his hand.. I detest SQL Mail with a passion..
CEWII
February 9, 2011 at 1:57 pm
The one big drawback with Database Mail for me is the inability to programmatically set Reply-To. We have a no-reply account I send with, and the emails all begin, "PLEASE DON'T REPLY TO THIS EMAIL," but of course that doesn't always work. I monitor the account for bounces, and end up wasting time copying important info the recipient put in his reply over to my CRM system's ticket. PITA.
Anyway, I got the check for send failures working thanks to your help.
February 9, 2011 at 2:01 pm
No problem, setup a non-default profile and specify it for this process when you call the sproc. you can have multiple profiles to handle different reply-to's and such..
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply