Email Bounce Automation Using BoogiePOP Enterprise - Part 1 of 2
page 4 of 5
by Scott Wolfington
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 29070/ 32

Configure BoogiePOP Enterprise To Connect To Your MS Access Database

Before starting this step, you'll need to know the name of your MS Access database file, and the folder you saved it too. The name of my Access database is email.mdb, and I saved it in my c:\temp folder. I'll be creating a "System DSN" that points to it. Your Access database is probably in a different location.

In order for BoogiePOP Enterprise to connect and log to your database, you must first create a "System" Data Source Name (DSN), on the computer running BoogiePOP Enterprise, that points to your database. To create a new "System" DSN, click the Database TreeView item within the BoogiePOP TreeView Window (see image below) for the BoogiePOP POP account you created in the previous step. Next, click the "..." button next to the DSN selection box. This will launch the ODBC Data Source Administrator Applet.


Sample BoogiePOP Database Connection Window



Click on the "System DSN" tab, and then click the "ADD" button to launch the "Create New Datasource" dialog.


ODBC Data Source Administrator Applet



Select the "Microsoft Access Driver (*.mdb)" driver and click "FINISH" to display the ODBC Microsoft Access Setup dialog.


Create New Data Source Dialog



Enter a data source name and description for your database. For this article, my data source name (DSN) will be "Email Bounce Log Database" and the description will be "BoogiePOP Enterprise Bounce Logging". You can name your DSN whatever you like. Next, click on the SELECT button to launch the "Select Database" dialog.


ODBC Microsoft Access Setup Dialog



Browse to your Access Database (Our example database name is called "email.mdb". Your database will probably be named something different), highlight it, and click OK. At this point, you'll be taken back to the "ODBC Data Source Administrator Applet".


Select Database Dialog



You will see a newly created System DSN called "Email Bounce Log Database". Click OK to return to BoogiePOP Admin.


ODBC Data Source Administrator Applet



Select the "Choose DSN" dropdown box to select your newly created DSN. If your database has a password protection system then type in your login and password for the database.


Database Screen after selecting our newly created DSN.



Entering your SQL Statement

Now that you have configured the DSN, it's time to enter the SQL statement. In order for BoogiePOP Enterprise to log bounce data to your database, you must enter the SQL statement that BoogiePOP Enterprise will execute each time it retrieves a bounced message from your POP account. To acheive this goal, we'll be using a simple SQL INSERT statement (see below). You can copy/paste the following SQL into the "SQL Statement" text box in BoogiePOP Admin.

SQL Statement
INSERT INTO bBounceLog (BounceType, PopAccountName, EmailAddress, RawMessageText) VALUES ('#BOUNCETYPE#', '#POPACCOUNT#', '#EMAIL#', '#RAWMESSAGE#')

Once you've entered the above SQL statement, your database settings window will look like the following for your BoogiePOP POP account:






Using BoogiePOP Variables

One important concept to remember when creating your SQL statement is the idea of BoogiePOP variables. A BoogiePOP variable is a data value that can be used anywhere in your SQL statement. They are most commonly used in the VALUES clause of an INSERT statement, the SET clause of an UPDATE statement, or as parameter values for a SQL stored procedure.

You can use a variable by typing it into the SQL Statement text area using surrounding pound symbols "##". For example, to refer to the variable "EMAIL", you would type "#EMAIL#". Variables are case-sensitive.

The table below lists the standard BoogiePOP variables that BoogiePOP can use within your SQL statements.

NameDescription
BOUNCETYPEThe bounce 'type' of the bounced message. (ie. SB, HB, GB, MB, MBKS, etc.)
EMAILThe recipient email address of the bounced message.
POPACCOUNTThe POP account that BoogiePOP retrieved the bounced message from.
RAWMESSAGEThe raw text of the email message BoogiePOP retrieved.


NOTE: There are several more BoogiePOP variables that you can use, but they are not covered in this article.


Testing Your Database Connection & SQL Statement

You'll need to test your BoogiePOP POP account database settings after you have selected your DSN and entered your SQL statement. To do this, click the "Test" button at the bottom of the POP account database settings window. If everything works correctly, you'll see a message like the one in the following image.


BoogiePOP Successful Test Dialog



To confirm that the "Test" button worked properly, you can also open your Access database and check to see if the "test" record was inserted into the bBounceLog table. Each time you click the "Test" button at the bottom of the POP account database setttings window, a record will be created in your bBounceLog table. If everything worked correctly, you'll see a test record in the bBounceLog table. See image below.


Successfully inserted test record



Enable This Database Connection and Sql Statement

Once you're done testing your BoogiePOP POP account database settings, you'll want to enable it. You do this by making sure the "Enable This Database Connection and Sql Statement" checkbox has a check mark in it. Selecting this checkbox will tell the BoogiePOP service to connect to the DSN, and execute the SQL statement for every email bounce message it retrieves. If, at anytime, you'd like to stop logging to your database then un-check this same checkbox.


Save Your Settings

In order for your new database settings to take affect, you must click the "Save" button at the bottom of the POP account database settings window.






View Entire Article

User Comments

Title: help needed regarding BoogiePop installation and setting up   
Name: Rakesh Verma
Date: 2007-12-24 1:21:55 AM
Comment:
Dear sir,

i am describing below my requirements and my experience with boogietools

case: i have an clasic ASP written website, i want to track bouncing mail address when i send newsletter to a list of mail id's.

my Experience : i installed "BoogiePop Enterprise Setup" and followed steps in
http://aspalliance.com/375_Email_Bounce_Automation_Using_BoogiePOP_Enterprise__Part_1_of_2.all
but unfortunately i could not get Pop Log under tree structure i can see "Bounce Service" and "Smtp Service" links over there.


my assumption : what i think is when you set up all settings and installed "BoogiePop
Enterprise Setup" on server then it will save all bounced mail id's to Access Database which have fields described in your article.

i am not sure BoogiePop is helpful in my case or not.
please guide me if i can use BoogiePop to fix my website.
also please describe me its price and subscription process.

my mail id is
rakesh.verma@dotsquares.com

Regards
RAKesh vermA

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-04-14 1:35:08 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search