Campaign Enterprise can connect to any ODBC compliant database, one that complies with Open Database Connectivity requirements. These include, but may not be limited to the following:

• MS Access
• MS SQL Server
• MySQL
• Oracle

There are three steps to go through before making data available to the end user for actually sending the campaign.

• Set up the database connection
• Select the table and and filters
• Assign it to the end user

Set up database connection:

• Determine your database type. The database connection is dependent on what database you intend to use for your email addresses. Go to connectionstrings.com and get the right connection string for your database. You will need to know the version of your database, the permissions used, the IP address and other information as specified by your database administrator. You may also need a username and password depending on how your database is administered.
  
  Example Strings: 
  Access: Standard Security: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Persist Security Info=False;
  
  Access: With Database Password: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Jet OLEDB:Database Password=MyDbPassword;
  
  MS SQL Server: Standard Security: Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;
  
  MS SQL Server: Trusted Connection: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
  
  MySQL Standard Security: Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
  
  These are just a few examples. Go to connectionstrings.com to find the string for your database.
• In Campaign, log in as the administrator and click the Administration link.
• Click Database Connection Management
• Click Add New Database Connection
  Name it something readily identifiable, "Main SQL Connection" for example
  Select the database type
  Enter the string, with any variables necessary already filled in.
  
  When you save the string, Campaign will automatically test the connection. You may have to tweak the syntax of your string as prompted to successfully connect.
• If you are in a multi-user environment, make sure you assign the users and groups that have access to this connection.
  

Create External Email List:
You can now create a new external email list, using this connection string. All available connection strings will show in the drop down and you would select the one you need for a particular list.

• Click Email List
  
• Click Add New Email List
• Name the list something readily identifiable "Sales Followup" for example
  
• Select External and click continue
  Select the database connection from the list of available connections
  Select the table, SQL Statement option, Stored procedure or use a filter in the project by selecting "From Campaign"
  Match the columns shown, Bounce, Unsubscribe, ID and Email
  Then save the Email List
• Enter a Bounce SQL statement if you want one.
• Set the permissions for this connection
  

There are other options available for more advanced list configuration should you need it. Please contact technical support for more information. The email list will appear in the campaign to address field for those users with permission to connect to this list.

By: Chris Lewis

When the first Arial Software products were create the database language of SQL was thought of to be too hard to use and just for programmers.  Many of the database operations that were done using our program were simple field/column updates, like incrementing a number in a record if the email was sent. The program was set up so that the field to be updated was in a selection box and the only operation you could do is increment the field by one. This functionality still exists today even in version 11, but as many have seen this simple operation is not sufficient.  Because of this we added the ability to run an SQL statement when an even happens in Campaign Enterprise, like when a record is sent, when an email is unsubscribed, or a click through occurs.  This feature can be turned on in the administration area which then causes the program to show this new SQL statement box on each even configuration screen.  Some have opted to, instead of incrementing a record, to insert a new record recording the event into a table that can be used for later reporting.  And example of this, say for an unsubscribe operation would be:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values ({CAMPAIGNID},{UNIQUEID},GetDate());

So, when a unsubscribe event happens for a particular email that was sent, this statement is sent to the database you are using and the values within the braces { } are replaced with real values.  The resulting statement that is actually sent to your database would look like this:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values (14,43456,GetDate());

After the operation is done, you will have a new record in the tblUnusubscribes table recording this event.  This data can now be used for filtering and/or reporting purposes later.  The advantage of this method is that it records the specific date and time of the event, and the data is separate from the original record.  Using this method is required when the original data cannot be modified due to security or database structure.

You can use any legal SQL statement you want for these events.  You could opt to update a record instead of inserting, or you could run a stored procedure to trigger other events.  More information is available on this subject on this website or by request.

By Chris Lewis

Many of the best features of Campaign Enterprise is the ability to write directly back to a database triggered by certain events. In the early days of email marketing many were using simple database structures so the writeback methods were simple field updates.  If a response event occurred, a database field would simply be incremented corresponding to a click through or unsubscribe event.  As time moved on, clients wanted more flexibility and the ability to add SQL statements (stored procedures) were implemented but the field update/increment method was kept.  I will discuss the differences of the two methods in order for you to figure which is the most appropriate for you:

Simple Field Update
Using this method, you simply select a field that you want incremented when a conversion event occurs.  If you need this simplicity and you just need to know if a recipient responded in some way that this will work fine.  One of the biggest roadblocks for this method is that your datasource, the table or view you specified, cannot be written back to for some reason.  These reasons can be as simple as the table is locked or the SQL statement you are using does not allow updates for a multitude of reasons.  Some of these reasons can be that your SQL statement is a one-to-many relationship, you are using grouping or aggregate statements, you are using a union query, etc.  Campaign Enterprise can only do as much as you allow it to do and many of the reasons for non-updateable data can be subtle.  If you just specify a table for your datasource field increment updates should not be a problem, but the key is to keep it simple.

SQL Statements (Stored Procedures)
This method of database updates give you complete control of the situation.  You are no longer bound to using the original datasource for updates, in fact, you can write back to any table you specify or perform mammoth size operations for a response event  like click throughs.  In Campaign Enterprise you can specify any SQL statement that your SQL Server will allow and Campaign Enterprise just passing that statement along to your SQL Server.  The only modifications that are done to your SQL statement is the replacement of "merge fields" in the statement.  For example, you can use this to record conversion events:

UPDATE tblMyClientList set EventEmailDate=GetDate() where ID={UNIQUEID};

If you put this statement in the particular event tab in your campaigns, when the event occurred this statement would be sent to your SQL Server with the {UNIQUEID} merge field replaced with the true unique ID value for that recipient.  As you can see, this is a free-form area and you could make the SQL statement anything you want.  You can even have a stored procedure run:

EXEC spTrackingEvent {UNIQUEID};

In that way, you don't have to put any code at all in Campaign Enterprise.  Also, no tables are exposed and your can even lock down the security in the SQL Server for this stored procedure.  There are other merge fields you can include in your SQL statement that Campaign Enterprise will replace, but it is too big for this article. You can request a list from us or look in the documentation for more merge field replacements.

There is a switch in the Campaign Configuration section in the Administrative area of Campaign Enterprise if you want to use this ability.  It is by default turned off to now confuse people when they first start to use the product.

By: Chris Lewis

We are asked frequently "what database should I use?"  Now since the question is being asked, usually that means you don't have one and the field is wide open. Though you can convert to other database types later, it is better if you can look at your future needs and plan based on that.  This might take a bit more cost and setup, but if you are planning on growing your lists and abilities, it is the best course.  The following is a list of databases with their advantages and disadvantages

Microsoft Access - This is a "file" based database which is a great start and may actually be all you need.  If you are primarily sending out emails, don't have a ton of responses events, than this database is great.  We have actually seen it work for many years for large situations.  An MS Access file is very stable, easy to backup (one file), and portable.  The disadvantages is that with heavy use the file can become corrupted but very rarely.  You do need to compress this file so that the database does not get too inflated, especially if you are adding and deleting a lot of records.  Some people have used an Access database for the database back-end for Websites, and that may be OK, but if it is used with an active webserver and Campaign Enterprise at the same time there can be a chance of deadlocks (trying to access the same info at the same time). Since many processes may be accessing the database file at one time without "each other's knowledge", there is the potential for collisions.  BUT, don't worry about this until it happens.  Access is a great solution and will be support by Microsoft for many years to come.  MS Access is not free but it is usually $100 or part of the MS Office Professional package.

Microsoft SQL Server - This system is a true client-server database. Since the server takes requests from many different processes and lines them up in a queue, these requests are handled in an orderly fashion and avoids a lot of the problems that a single-database file has.  Advantages are that the SQL server can be installed on the same or a different computer than the one running Campaign Enterprise so you can distribute the workload.  It also allows you to have your webserver, database, and email sending system separated.  We always recommend you use an SQL server like this whenever possible.  This system can cost from $995 and up for MS SQL Server 2012.

Microsoft SQL Server Express - This free database functions just like the full SQL server above, but they have a database volume size limitation and it will only use on CPU core.  The limitations of the Express versions change each release, but many will find this system to be more than sufficient.

MySQL - This free database is created and maintained by the public domain.  Many people use MySQL with Campaign Enterprise successfully.  Most of the time people use ODBC to connect to the database but there have been OLEDB providers available too. MySQL will use multiple cores on your computer and there is not a limit on the database volume size like MS SQL Express.   If you don't like Microsoft products and want a good free SQL database then MySQL is a good choice.

Oracle 11g - Oracle works well with Campaign Enterprise. You will need to be experienced setting up database connections using their ODAC connector.  You can use a personal version of Oracle for free.  The full versions of Oracle have a lot of different pricing structures so it is hard to quote.

DB2 (AS 400)  - This database is for IBM mainframes. Campaign Enterprise has connected to this type of database but it is mostly used large companies with established systems.

Many Others - Campaign Enterprise can connect to any database that has an ODBC or OLEDB connector.  We have successfully seen it work with Foxpro, Filemaker, and even text-based files. These databases many times do not allow for outside writebacks so if you are trying just read information from a database than these will work but they are not recommended.