January 7, 2008 at 4:33 am
Hi there All
Im hoping someone can give me some pointers where I might start with this...
Part of an application I have here has sql server banging off emails, thats fine, works well. Lovely 🙂
However, emails may get bounced back to us as non working/marked as spam/etc.etc.
Emails will bounce back to an exchange server we have here.
Does anyone have any pointers to how I might go about processing this back into my sql server so I can flag my relevant records with the bad emails as `not working`? Googles not my friend today 🙁
Im guessing either exchange can call something on my sql server (not preferred method), or sql server can call an exchange server to extract data/emails, or maybe I should have emails come back via a different route. Or something else! (Need to do this in house rather than buying in a 3rd party client.)
Many thanks in advance,
Martin
January 8, 2008 at 12:25 am
maybe you can investigate the 'old' sp_processmail, but keep in mind MS anounced it to be removed in futer versions.
Or maybe write your own vb.net or vbs application to read the mailbox and process it to sqlserver.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2008 at 1:19 am
Don't build this one yourself that's for sure, email returns can take a variety of formats, etc. - there are multiple tools out there from free to commercial that can process returned emails and update your database accordingly, google for "email list management".
January 8, 2008 at 2:29 am
Actually theres another route that ive discovered, which seems the proper way to do it and doesnt seem that complex.
Called `Event sinks`, and are there for just such occasion!
A bit of scripting linked to our exchange server on the equivalent of a `mail received` event and bobs your uncle. Ill get back with what I do incase anyone is interested 🙂
Many thanks
Martin
January 8, 2008 at 3:06 am
That would be nice 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2008 at 10:06 am
Watch yourself with Event Sinks as they've shown a habit of changing between versions of Exchange and many of the "old" events that you used to be able to "attach" to in Exchange 2003 no longer work/exist in Exchange 2007. Not sure if there is any documentation as to how/why they're different in Exchange 2007 but I know that the "catch all" script that we had running on Exchange 2003 won't work on Exchange 2007 and there is no way to accomplish the exact same functionality anymore.
J
January 8, 2008 at 4:28 pm
Don't go the event sink route. Aside from the architectural changes from Exchange 2003 to 2007 which could require you to rewrite your old sinks entirely you will likely end up with code that is a nightmare to maintain. There are literally thousands of different bounce message formats - just think for a sec how you are going to code for them all? Roll your own regex? Fine - but you will need to get a sample of every possible format and test it with your regex.
And that's aside from the many performance and stability problems you are likely to get running fat, complex (read: fragile) event sinks on your Exchange box. Don't reinvent the wheel - this problem is an old one and there are companies out there that tackle this problem as their core business. Use one of the off-the-shelf products that black-boxes the bounce message identification process so you can feed the results into your DB.
We originally started out with event sinks to address this issue back in the Exchange 5.5 days and used them up through Exchange 2000 but ditched them around the time Exchange 2003 came out. They were just too hard to maintain (always tweaking the regex to cope with another new bounce format we'd missed) and a bit unstable. We moved to a product called BoogiePOP (now BoogieTools Enterprise[/url]) - there are others out there like B*Bounce (not sure if they are around any more), etc. - which runs as a Windows service on any box and polls a mailbox via POP3 on your Exchange server every few mins to download your bounces. It runs them through its bounce library ID process and categorises them into one of a few categories (hard bounce, transient bounce, etc.) and feeds the result into any arbitrary proc/query via ODBC. Very happy with it so far (3+ years on now).
Regards,
Jacob
January 9, 2008 at 10:22 am
Jacob, thanks a lot. I checked the price on that product it it's dirt cheap, offers a 30 day trial, etc. It looks like a winner.
Cheers
January 10, 2008 at 4:16 am
Very interesting im going to investigate too 🙂
Many thanks
martin
January 11, 2008 at 12:22 am
Interesting indeed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2008 at 2:56 am
Might be worth having a look at the solution used over at SQLTeam.com
January 13, 2008 at 3:25 pm
Yep, they're using BoogiePOP too. It's really straightforward: setup your Reply-To: & From: addresses on all your mailers to go to different mailboxes, configure BounceStudio with your bounce proc pointing at the mailbox with the From: address as its alias, and set it up to forward non-bounces and grey categories (challenge response, unsub, etc.) to a human for action.
Regards,
Jacob
January 14, 2008 at 8:44 am
Incase anyone is interested, I thought id further the DIY thread 🙂
Looking around at various solutions, for those interested in doing it by hand, there are a few methods you can try!
Event Sinks
These reside directly on an exchange server. You can tie in your own scripts/code (e.g. write your own VB Script to handle things). You can set exchange to e.g. `on receiving a message to address xxx@yyy.zzz' run your bit of code.
It all sounds very nice, but incomopatibilities between versions of exchange (2000,2003,2007) reared their ugly head. Write something now - upgrade to 2007 - well it might work. It might not. Suffice to say there are enough warnings around to put me off this completely 🙂
the other solutions are all the equivalent of writing your own external client to process the mail. (So handy for keeping processing load out of the exchange server)
POP3 based processing
There are plenty of 3rd party packages around that connect via pop3, download and process as necessary. However, thats not DIY 🙂 And if your like our place, you don't have a nice pop3 service enabled to be able to do it.
Custom processing of pop3 is possible, but you are going to have to do a lot of DIY processing of communications. Of course this will work with any pop3 mail server!
MAPI/CDO
This appears to be the big daddy of processing with exchange. These are libraries/api's that let you connect more directly to the exchange server. You get some nice pretty objects you can play with in your code that automatically return your messages, their contents, etc.
However (you didnt think it would be simple do you :)) - MAPI/CDO isnt supported with .net (what im using to code this with). Older VB6 etc. - yes.
You can actually get it working with .net, but you have to mess around with getting references to libraries correct. And there are different versions depending what machine your code is running on (e.g. outlook based libraries on your local machine, and exchange variants on your exchange server). They are not 100% the same (e.g. on kerberos authentication, and how security is handled.
Our place wants to disable the MAPI support on exchange too! Got to love it 😀
WebDAV
This is a html based means of communicating with an exchange server, as used (I belive) by webmail clients.
However, once connected, processing is down to passing XML between your client and the server - which of course introduces complexity and overhead, as does the html method of communication.
My main conclusion....
It all sucks somewhat, and MS could do with releasing some better .net support 🙂
Me - im going to try getting the MAPI/CDO method going again here. Then comes the fun part of processing stuff and hitting the databases 😀
I can get back with further developments if anyone is interested 🙂
Many thanks
Martin
January 14, 2008 at 8:57 am
Martin, I just had success writing .NET classes in VB2005 and creating a COM wrapper for an older VB6 application. The .NET class uses framework 2.0 libraries and it's working great. I'd skip MAPI and use the 2.0 framework, it should work in VB6 just fine.
If you're going to write code to add a listener for mailbox, I'd be interested in what you find out there in the DIY space
Good Luck
January 14, 2008 at 3:47 pm
Unfortunately most of the APIs Martin lists are deprecated in Exchange 2007 (or in some case removed). Have a look at the 'Deemphasized Functionality' section here http://technet.microsoft.com/en-us/library/aa998911.aspx for details...
To write a store event sink (Exchange 2003 or older) you use either CDO 1.x (which is a MAPI COM wrapper really) or MAPI directly, or utilise the ExOLEDB provider via ADO or CDOEX. All of these APIs are deprecated in 2007, and event sinks themselves no longer exist as such. Even WebDAV is going :ermm:
There are the new Exchange web services replacing all of these, and you've still got the old-skool methods (POP3 & IMAP) - but the hard part of this problem is not how to access your mail but how to identify the bounces. It still boils down to using an effective pattern-matching algorithm against a stream of text. Granted, it's an interesting exercise, but unless you have a lot of time to dedicate to it you may find it more pain that it's worth.
Regards,
Jacob
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply