email validation

Question

email validation

SSC Enthusiast

Points: 180

January 13, 2004 at 2:25 pm

there is SP which lets to send email:

EXEC xp_sendmail 'emailaddr', 'The master database is full.'

If email address is incorrect it genarates an error. Is there any way

to validate email address by T-SQL?

I do not want to use xp_sendmail if emial is not valid.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Sort of a Moment 22 then?

How would you know if the address is valid without trying to send it? Only the mailserver knows if it can send the mail or not.

However, if you wonder about how to validate a string to be conformant to a valid address (ie that the 'format' is right), then yes, you can do this in T-SQL.

Personally, I store emailddresses in a column which has a CHECK constraint that prevents malformed addresses to be entered.

That way I don't have to validate when I pick them up from there.

It looks like this:

ALTER TABLE dbo.myTable

ADD CONSTRAINT CHK_email CHECK

( = ' ' or is null

or ( like '[a-zA-Z0-9]%@[a-zA-Z0-9]%.%[a-zA-Z0-9]')

and ((not(( like '%[ ]%'))))

and ((not(( like '%@[/\$#!~&(){},;*=]%'))))

and ((not(( like '%[[]%'))))

and ((not(( like '%..%'))))

and ((not(( like '%]%'))))

and ((not(( like '%@%@%')))))

BTW, while I'm at it.. Can anyone point me in the right direction where I can change the webinterface for posting to a more userfriendly format?

Currently I have a single line and have to type HTML tags by hand to get anything near readable This really s*cks bigtime.

=:o/

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Which browser do you use?

While not every option in this word processing look alike answer page works for me, at least I don't have a problem with new lines

Btw, while I'm at it, when will you post your first 'It depends....' post, Kenneth

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

alex_1009 SSC Enthusiast Points: 180 More actions · Answer 3

Unfortunately, just string validation does not solve everything. I need to make sure that, for example, email smith@aol.com is not misspelled as smithhhh@aol.com.

alex

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Well, Frank... It depends...

I use IE as browser, and just click the 'reply' button (or 'quote')

However, I onle get a single row in the webform (or page)

and I can't for the life of me find anywhere I could type

SELECT settings

FROM thisStupidWebThing

WHERE easeOfUse IS NOT NULL

Sorry to be.. well.. I just don't like webforums... Mailinglists is more to my liking.

I have to admit though, that this is an opportunity for me to refresh my notepad-style HTML coding

=;o)

/Kenneth

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Ok, but how are you gong to do that?

I'd suggest that you just try to send the mail, and if it bounces, trap that

and display or log the offending address for later analysis.

Short of the implementation of something like fn_crystalBall() or similar, I really don't see how a piece of code could read the mind of someone sending the mail.

For all I know, smithhhhh@aol.com may very well be the intended receiver, even though smith@aol.com also exists, right?

=;o)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Strike!

SELECT

settings

FROM

thisStupidWebThing

WHERE

easeOfUse IS NOT NULL

That's really sad, I really appreciate following your mails.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

Thanks

Well, I'm over at sswug.org most of the times anyway, just thought I'd check how things were over here.

But if I can't get this formatting stuff in some order I probably wont' post anything lengthy or overly complex

I'll try to ping Brian and check up on the subject.

=;o)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

I know! I'm a silent follower of the SQL2k mailing list

Formatting works somehow, but isn't that user-friendly.

Let's hope for the best!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

alex_1009 SSC Enthusiast Points: 180 More actions · Answer 9

I think I did not define question clearly. The store procedure xp_sendmail is used on the back end on SQL Server. It is not related to any browser. I need to limit emails by email list stored on exchange server. This is a part of SQL Server job. I may not send email to outside address.

alex

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 10

Sorry Alex for the off-topic drift into browsers.

Back to your mail problem then.

The thing is that SQL Server has no clue about what addresses the Exchange server will actually send or not.

I would say that there is no way that you in advance can validate the email address - short of keeping a list in a table in SQL Server.

Then you can check against this table before you call xp_sendmail.

Note, though! There is still no guarantees that the mail will actually arrive, or that Exchange will not return an error.

And you also must maintain the addresses in such a lookuptable in order for it to be of any use.

=;o)

/Kenneth

alex_1009 SSC Enthusiast Points: 180 More actions · Answer 11

Kenneth!

Thank you for your reply.

After we set up SQL Server as an e-mail client, SQL Server let us confige an agent for a job. We can open a whole list with e-mails for for an agent if we use ER for configuration (management->operators->prorerties; button "Email name"). And yet there is no way to get this list via T-SQL ???

Alex

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 12

Not that I'm aware of - (that doesn't mean there isn't a way however)

Thing is I ditched SQL Mail long time ago and now uses XPSMTP instead http://www.sqldev.net/xp/xpsmtp.htm

SQL Mail relies on MAPI, XPSMTP is a simple SMTP mailer.

I haven't regretted that move even once.

SQL Mail could be a real pain in the b*tt sometimes.

Sorry, but I don't think I have any more useful thoughts about how to retrive the address list from Exchange in T-SQL.

I doubt it can be done, at least in a simple way.

=;o)

/Kenneth