Avatar of steven
Flag for United States of America asked on

Sql 2012 DB_send_mail sends mail out but at times it doesn't and fails them with the above error. Can anyone help.

Emailing from the Database using DBMail sql 2012 we are seeing many emails run fine while others error. We currently run sql 2012 and have used DB mail for about 4 years but the past few months we are seeing emails not sending with the below:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-01-21T09:34:01). Exception Message: Cannot send mails to mail server. (Failure sending mail.).

Keep in mind this only happens here and there, yesterday 105 didn't send of well over 300. With the above error. OH and we are using Office 365, we have scoured the internet for a solution but nothing.

Microsoft OfficeMicrosoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
Peter Chan

Can you check the relevant Email server is working fine? Even if you're sending out message from Database, it does make use of the service of that Email server there. Is there any change like account password leading to the problem?

You definitely need to work on the error resolving/reporting properties of the mail sending code. It's got an exception coded, but it leaves out the exact details.
After we know more, we know if the other side is not reliable, or you're offending the server (for example, server rejection due to spam content, rate limiting issues etc)
If you want more reliability, maybe use a specialized email relay service with API's (Sendgrid, it's not that expensive), you'll get better reporting on the sending, and on the receiving part (sometimes even extra data if recipient allows tracking)

Not sure I follow the meaning, "work on the error resolving/reporting" we pull the error from sql.  How can improve it?  Please let us know.  The email server is set up to work properly after reading everything on Microsoft concerning rate limiting, since we do send many emails from the database.   We don't know who to turn to, does anyone know where we can get help?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Have better error codes built in your code.
Also you may try to use a packet sniffer (Wireshark) to see EXACTLY what's going on during these email exchanges.

Ok we understand and thank you we will look into a packet sniffer.

Packet sniffing only works with NON-(SSL/TLS) SMTP traffic. For debugging, you need to drop to this level for the time being.
Also, if you have 105 failing out of 300, and later on you RETRY those exact 105 messages, what happens?

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

The ones that error i manually have executed the procedure (stored proc) and they then are delivered.

Try to keep a list of the failing email addresses. If after a few runs, you'll see a pattern (for example, ALWAYS gmail or hotmail addresses), it's still a rate limiting issue (not on your side, on the recipient's side). Then you have to add more pause between email sending.
Still the best way, to remove any analyzing in the future, is to have the EXACT SMTP error visible at all times. But as I have no access to your code, I don't know if that's possible (depending on which library you used).

Sorry there is no rhyme or reason as the emails that have failed they are all over the place some internal some external.  Nothing is the repeated except the error i posted
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Then still eithe Wireshark will have to show connection problems (lost packets, remote server not responding), or you have to have your code return the correct SMTP error code AND message (as SMTP error codes are sometimes customized, and you still won't the exact error without the accompanying message, for example situations like greylisting).

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.