Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
 Posted - 2002-12-09 : 14:30:50
|
One of the things I've struggled with since the start of this site is the newsletter. Initially I had it hosted through Microsoft's bCentral but that required a signup process that wasn't integrated into the site. I finally wrote my own mailer program and moved it all in-house. That only left one ugly problem with the newsletter: email bounce processing.Early on I did this by hand. I'd just open the bounced email and click on the unsubscribe link. Now that I'm over 5,000 subscribers that doesn't really work anymore. I tried writing code to handle this myself but quickly gave up. There are just too many email bounce formats for me to handle. Then I stumbled across BoogiePOP by a company called BoogieTools. They have a free demo you can download to try out.It installs as a service on a PC. I have it running on my demo machine here at the home office. The first step was to point it to a POP3 mailbox that will be recieving the email bounces. I set it up to check the mailbox every 60 minutes. You can configure this however you'd like. BoogieTools' web site says the product recognizes over 700 email bounce formats. I think I probably saw every one of these while I was doing it by hand.The second step is to tell it what to do with the bounces. You pick a DSN and give it a username and password and a templated SQL statement. When I looked at different newsletter packages I looked for strong SQL Server support. Many of them supported SQL Server but you were forced into their database format. By using a tempalted SQL statement BoogiePOP conforms to my database layout. My SQL statement looks like this:exec E_ProcessBounce @sBounceType = '#BOUNCETYPE#', @sEmailAddress = '#EMAIL#', @sPOPAccount = '#POPACCOUNT#' Everything inside the # signs is filled in by BoogiePOP prior to sending the statement to the server. In my case, all the bounce processing is handled by a stored procedure. You could just as easily write an INSERT, UPDATE or delete statement. Each installation can handle up to 5 POP3 accounts and each POP3 account can have it's own database connection so you can handle multiple newsletters with ease.It's been sitting in the corner running through two newsletters now. It hasn't needed any configuration changes or maintenance at all. All the changes that I've made to bounce handling have been inside my stored procedure and didn't affect BoogiePOP at all. Since it runs as a service reboots don't affect aren't a problem. It runs locally and connects to the remote mailbox and remote database without a problem.If you're sending out any type of automated emails this is a great product. It does exactly what it says it will quietly and effeciently and integrates very well with SQL Server. It would make a great companion for a Notifcation Services application.If you want something even more specialized you can download the BoogieBounce API. This will let you write your own customized bounce processing solutions.As a result of this you should start seeing newsletters on a more regular basis. In the past it would take me 30 minutes to compose the newsletter and an hour to handle the bounces. Now I've got that hour back. |
|
|
|
|
|
The non-bouncing newsletter