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.

As your database grows in size and complexity, it may affect the performance of your email marketing campaigns. Frequently, performance issues appear to some people as a sudden change, causing them to blame the program for the issue, when the problem may be much deeper. One of the best ways to improve database and campaign performance is to index your fields, especially the fields you are using for merging and updating.

According to Wikipedia
A database index is a data structure that improves the speed of data retrieval operations on a database table     at the cost of slower writes and increased storage space. Indexes can be created using one or more columns     of a database table, providing the basis for both rapid random look ups and efficient access of ordered             records. The disk space required to store the index is typically less than that required by the table (since             indexes usually contain only the key-fields according to which the table is to be arranged, and exclude all the     other details in the table), yielding the possibility to store indexes in memory for a table whose data is too         large to store in memory.

In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only store the upper case versions of the last_name field in the index. Another option sometimes supported is the use of "filtered" indexes, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expressions formed from an assortment of built-in functions.

When I worked on running dive meets (springboard and platform diving) for my daughter, we had a system where people would turn in dive sheets based on age brackets, gender, and diving level.  At first, we had just one inbox for the divers to turn in their dive sheets. With a total of 50 divers we could answer the questions of how many girls vs. boys are there, how many 10-11 years olds, how many novice or junior Olympic levels are there. We had to cycle through the whole pile to get those questions answered each time, but we could answer the questions pretty quickly. As the season progressed, we started having more divers come, and when it go up to 500 divers, having the one in box was crazy and it took a lot of time to answer those questions.

In the same way, using a database, a small number of records within a table can be managed pretty efficiently without any indexing.  When a question is asked of the database (filtering), the database system cycle throughs ALL of the records noting which records should be included based on the filtering criteria (ie: State='CA').  The time it takes to do this is unnoticeable until about 10 thousand records or so.  Now you start noticing a lag time, but, it is still acceptable and you are too busy to deal with it.  Time moves on, the number of records in your table is increasing (which is good!) and now your waiting 10-15-20 seconds. Then someone imports 50,000 records into the table and boom, you start getting timeouts.  You say, "this worked this morning!  And I did not change anything."  You have just crossed over into the "Indexing Zone."
 
These types of issues sneak up on you unless you have a completely static list of people to send email too. It will happen!  Now even the most seasoned database programmer can get caught in this trap.  The only way to really make sure things are in tip-top shape is awareness and maintenance.

Indexing is kind of like presorting.  Say you have a table with basic contact information in it (name, city, state, etc.).  When you add a record to the table without having any indexing, all you are doing is simply appending a piece of data onto the end of the table, no order, no nothing. Contact your database administrator today to start indexing your data and improve your email marketing performance. 

Here is a sample MS Access database mdb file you can use.

There are instances in an email marketing campaign where you may need to compile customer information out of several tables in your database, and have it appear in a dynamically formatted list in the email message.

Using standard merge fields is possible, but this often results in unwanted spaces or line breaks. The way to manage multiple data displays effectively is to use a stored procedure to concatenate the data prior to inclusion in the merged email message.

We will assume that you have a table that contains the Customer's unique ID from your regular customer table, the name of the product purchased, the name of the customer and the email address. I have named my table PurchaseDetail and the schema looks like this:

CREATE TABLE dbo.PurchaseDetail
(
ProductID int NULL,
CustomerID int NULL,
ProdName varchar(100) NULL,
CustName varchar(100) NULL,
Email varchar(100) NULL
) ON [PRIMARY]
GO

Note: There is no primary key and no default values for fields. One row is inserted for each purchased item.

We will use a stored procedure to create a row in the NewPurchases table for each unique CustomerID in the PurchaseDetail table. The stored procedure will then process each row of the PurchaseDetail table and update the Purchases field of the NewPurchases table and append the purchased item for that customer to any existing purchased items for that customer.

NewPurchases:

CREATE TABLE dbo.NewPurchase
(
CustID int NULL,
CustomerName varchar(100) NULL,
Email varchar(100) NULL,
Purchases varchar(8000) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.NewPurchase ADD CONSTRAINT
DF_NewPurchase_Purchases DEFAULT '<br>' FOR Purchases
GO
COMMIT

Note: There is no primary key and the Purchases field has a default value of <br> so the purchased items appear in a column in your HTML email message. Also, the limitation of this field is 8000 characters. If you have more than 8000 characters of product sold to one customer, you will have to think of an alternative to this stored procedure. When this stored procedure is run, you will be able to merge the Purchases field into your Campaign Enterprise message body and have a list of products purchased by one customer. You will also have the customers name to use as a merge field in your message such as, 'Dear {CustomerName}', to personalize your message. The email address and unique id to use on the Data Source page of your campaign are also available.

Please consider another limitation of this solution. This solution is a one-time option. For instance, a scenario where you need to send more than one email to a specific customer, you will need to consider the filters to employ to have the correct list of products merge into the Purchases field. Using a date/time field is the easiest, by allowing you to filter the PurchaseDetail table on only products sold within a time frame e.g. the last month and the email is sent once a month.

Stored procedure BuildContent:

CREATE PROCEDURE dbo.BuildContent
AS
DECLARE @CustID int
DECLARE @PurchaseItem varchar (100)
DECLARE @Email varchar (100)
DECLARE @CustomerName varchar (100)

SET NOCOUNT ON

Insert into NewPurchase (CustID) (select distinct CustomerID from PurchaseDetail)

DECLARE BuildCursor CURSOR FOR
SELECT CustomerID, ProdName, CustName, Email FROM PurchaseDetail

OPEN BuildCursor
FETCH NEXT FROM BuildCursor INTO @CustID, @PurchaseItem, @CustomerName, @Email

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE NewPurchase SET CustomerName = @CustomerName, Email = @Email, Purchases = Purchases + @PurchaseItem + '<br>' WHERE CustID = @CustID
FETCH NEXT FROM BuildCursor INTO @CustID, @PurchaseItem, @CustomerName, @Email
END
CLOSE BuildCursor
DEALLOCATE BuildCursor
GO

This is just one example of using a stored procedure with Campaign Enterprise. Stored procedures are not for everybody, as you can see they are quite technical and require a lot of trial and error. Once created however, stored procedure calls on your database can greatly reduce data management workload and improve overall email marketing efficiency.