Dealing with incoming mail

Question

Dealing with incoming mail

Scott-144766

SSCarpal Tunnel

Points: 4333
More actions
April 29, 2009 at 6:33 am

#137209

I've been asked to investigate the feasibility of enabling our database to process incoming emails - i.e. read the body of the email and update records accordingly. Can anyone point me in the right direction to start looking? A quick tour of Books Online shows me procedures like xp_readmail with notes to avoid using them but no pointers as to what I should be using instead.
Thanks
--
Scott

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

SQL Server has a stored procedure called sp_processmail, which you can use. Behind the screens it will call xp_readmail, xp_sendmail etc. For security reasons I usually create a copy of sp_processmail, there I do some extra checks like what's the subject of the incoming mail. I also limit the possible actions and remove the @set_user variable.

Biggest drawback of this procedure is that it only works with SQL Mail (with Mapi profile) and not with Database mail (SMTP).

Hope this helps, for more see http://msdn.microsoft.com/en-us/library/ms189829(SQL.90).aspx

[font="Verdana"]Markus Bohse[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 2

SQL 2005 isn't really built for that. The features worked so poorly in SQL 2000 (too many security holes, etc.), so they pretty much replaced it in 2005. DBMail works much better for sending mail than xp_sendmail, but there isn't a replacement for handling incoming mail.

I think Microsoft's basic expectation is that you'd use Exchange and Sharepoint to handle that, and then have them connect to the database for whatever was needed there.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 3

That's more like the answer I'd expected than the answer I'd hoped for, but thanks.

--
Scott

Lowell SSC Guru Points: 323518 More actions · Answer 4

this sounds like something perfect for a CTE to do. expose an SMTP dll and read an email account.

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!

GSquared SSC Guru Points: 260824 More actions · Answer 5

Lowell (4/29/2009)
this sounds like something perfect for a CTE to do. expose an SMTP dll and read an email account.

CTE or CLR? I think you mean CLR.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 6

doh! CLR is what i meant....I just grabbed a sample project off of codeproject.com to see if it's easy to do...

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!