Check that an email address actually exists before emailing

Question

Check that an email address actually exists before emailing

davidandrews13

SSCertifiable

Points: 6808
More actions
June 4, 2010 at 5:43 am

#222321

Hi people!,
i'm wondering if the following exists.
I have a SQL job that runs every 30 minutes or so. It checks some data in the tables (that are inputted by our Employees) and based on what it finds it will send emails out to people.
The problem starts when an email that we have stored on our system is incorrect. Someone may have incorrectly written an email as 'myEmail02020@hotmail.com' instead of 'myEmail0202@hotmail.com' (presuming that the second email account exists and the first doesn't).
if i attempt to send to an email account that doesn't exists the email gets bounced back and forth as it continues to try and resend without any luck.
is there a way that i can check that the account exists before sending to it?
thanks all.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

no sorry Dave; all you can do is check that the format of the email is correct...[something] @ [a correct TLD]

If you have your own mailserver for davidandrews.com,for example, then you could potentially check your own mail server to see if anyname@davidandrews.com is a valid, setup mailbox, but not emails that are outside of your own domain.

spammers would love the ability to automatically check any email address's validity before sending.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

No, you can't.

What about using the "on failure action" in the agent job?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 3

thanks for the responses.

so if i wanted to find out if 'lowell@davidandrews.com' exists on my own server how would i go about that?

lets say that the email in the database was 'tommysmith@davidandrews.com' but the actual email address was 'thomassmith@davidandrews.com'.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

It depends. . .

SELECT ...

FROM ...

WHERE EXISTS ...

??

Without knowing how your mailserver stores anything, I'll give you that to get started.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 5

davidandrews13 (6/4/2010)
thanks for the responses.
so if i wanted to find out if 'lowell@davidandrews.com' exists on my own server how would i go about that?
lets say that the email in the database was 'tommysmith@davidandrews.com' but the actual email address was 'thomassmith@davidandrews.com'.

it depends on your specific setup;

you can query either Active Directory or an Exchange Server itself for information. If you had a mail server like MailEnable, the access might be a little harder, or at least I've never done it so far.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

select * from openquery

(ADSI,'SELECT cn, mail, co, distinguishedName, displayName

FROM ''LDAP://YourDomain"

where objectClass = YourDomain"\lowell'' '

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 6

davidandrews13

SSCertifiable

Points: 6808

June 4, 2010 at 6:16 am

#1176471

at skcadavre, fair enough 😀