Database email functionality

Question

Database email functionality

sudhakrishnaraj

SSC Rookie

Points: 26
More actions
September 24, 2018 at 9:25 am

#368807

Hi,
SQL Server 2016 is not capturing invalid email id in the sysmail_faileditems.
If the domain is not valid it is capturing in the faileditems. But if the email address is not valid, it says sent successfully.
Is there anything to do in settings to capture the invalid email address too
for example: abc@abc111.com is not captured as failed
abc@abc111.com? is captured as failed.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

An invalid email syntax will be caught by sp_send_dbmail and flagged as an error. If it looks good, the email is sent to the SMTP server that you have configured. But is this an SMTP relay? How many servers does it go through before it gets to a real mail server, that will actually get a nondeliverable response?

Open an email message and go to File -> Properties to examine the internet headers. How may hoops did it jump through?

SQL Server knows whether the next link in the chain accepted the email, it doesn't know what happens after that.

nimmagaddakrupanandh SSC Journeyman Points: 77 More actions · Answer 2

it is a SMTP Realy and there is no server in between DB SMTP.

nimmagaddakrupanandh SSC Journeyman Points: 77 More actions · Answer 3

Here our scenario is :

Hi,
SQL Server 2016 is not capturing invalid email id in the sysmail_faileditems.
If the domain is not valid it is capturing in the faileditems. But if the email address is not valid, it says sent successfully.
Is there anything to do in settings to capture the invalid email address too
for example: abc@abc111.com is not captured as failed
abc@abc111.com? or abc@abccom like that is captured as failed

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

The invalid domains are captured in sysmail_faileditems, because the invalid domain can be detected by checking the syntax of the address.

The valid-looking addresses that are undeliverable are not captured, because SQL Server does not follow the whole email process to find out what happened. If the email looks valid SQL Server tries to send it to the SMTP relay. If this delivery succeeds (to the next link in the chain), the email is successful as far as SQL is concerned. The SMTP relay likewise does not know about undeliverable addresses, it just forwards the message to the next server. Looking at the properties of an email I received from outside my organization (medium-sized company), it goes through at least five email servers along its journey. Only the final destination email server can decide that the address is not deliverable. A reply is generated that it failed, and that reply will be fed back through all the email servers involved. If it originated with an email client like Outlook you will see a "Message is undeliverable" response, because the Outlook client has nothing better to do than sit there and listen to the email server. SQL Server quit paying attention as soon as the initial handoff was complete.

What happens with any reply to a SQL Server email? You configure an email account with a reply address, but SQL Server does not monitor that inbox. Undelivered email messages are just like replies. You would have to write your own program to monitor the reply inbox, look for email bounce responses, and do something with the information.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

If you want to capture the email failures, just enter your own email address as the reply address for all SQL email accounts.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Scott Coleman - Tuesday, September 25, 2018 7:27 AM
If you want to capture the email failures, just enter your own email address as the reply address for all SQL email accounts.

Better than that, create an email distribution group on the email server so that you don't have to change code when you leave. They'll just change the members of the email group and it'll be a done deal.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nimmagaddakrupanandh SSC Journeyman Points: 77 More actions · Answer 7

But if i gave my mail ID in reply email also ,that failled items table is not update right . i want to get the failed mails into failed items table. So that it will be helpful for us.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

nimmagaddakrupanandh - Wednesday, September 26, 2018 1:40 AM
But if i gave my mail ID in reply email also ,that failled items table is not update right . i want to get the failed mails into failed items table. So that it will be helpful for us.

Like I said, create an email distribution group so that it's not for an individual and send as that. And like has already been said, SQL Server is not capable of figuring out which emails failed after they were sent to the email system. You'll need to make it so that sql server can read wherever you get your failed messages back from and have something read them for what the failure is.

I've not done such a thing or I'd give you some good detail on how to do that,.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jasona.work SSC Guru Points: 50117 More actions · Answer 9

You're asking SQL to do something it's not designed to do, behave like a "real" e-mail client (Outlook, Thunderbird, etc.)
With the addresses you've given as examples, the first one, the one you want to show in the failed items table, *IS* a valid e-mail address, based solely on the formatting, which is all SQL has to go on.
SQL isn't going to receive the bounce-back from the downstream e-mail server, that e-mail (that you would see if you tried sending from your desktop e-mail client,) simply vanishes into the network because there's nothing (currently) on the receiving end.

As Jeff suggested, if you want to see such failures, you're going to need to use a valid reply-to address and then someone will need to manually review them. There is NO WAY to get failures such as you're talking about to be put into the failed items table.