How to Create a Mailing List in Commerce Server List Manager with a SQL Query

When you use the Commerce Server Business Desk List Manager module, you can create a mailing list to be used with a Direct Mail campaign. To create the mailing list, you can import user information from an Analysis report, an imported file, or a SQL query. This article describes how to use a SQL query to create a mailing list.

A List Manager mailing list should contain the following two fields in the following order:

• rcp_email
• rcp_guid

If these two fields do not exist within the mailing list, in the order listed, your list will always have a zero under the Recipients, Users, and Emails columns within the List Manager even if you supply a valid SQL query.

NOTE: A mailing list must contain at least the rcp_email field for the list to be successfully imported into List Manager.

Before you create a mailing list, you must decide which table you want to query. The table must contain the required mailing list information. You could consider the following tables:



NOTE: The data warehouse RegisteredUser table is not populated with data until you use the User Profile Data Import DTS task to populate your data warehouse tables with user information. If you do not run this task, the data warehouse will contain either no user data or outdated registered user information. For more information about the User Profile Data Import DTS Task, see "Importing Data into the Data Warehouse" in the Commerce Server Help.

After you decide which table to use for your query, you can create a mailing list by performing the following steps:

1. Click Start, and then click Business Desk.
2. Click to expand the Campaigns option.
3. Click List Manager.
4. On the toolbar, click the Import List button (or just press ALT+I).
5. Click to select either a Static or Dynamic list. Static lists are saved lists that do not change unless they are re-created. Dynamic lists are created during run time, and can change if user information is added, modified, or deleted from the database.
   
   
6. In the Type name of list to copy to text box, provide a name for your list.
7. In the List Source drop-down list box, select From SQL Database.
8. In the Connection String drop-down list box, click either SQLOLEDB provider or SQL Server Driver. If you use the SQLOLEDB Provider, your connection string will resemble the following:
   Provider=SQLOLEDB.1;Data Source=<Server>;User ID=<username>;Password=<strong password>;Initial Catalog=<site>_commerce
   If you use the SQL Server Driver, your connection string will resemble the following:
   Driver={SQL Server};Server=<Server>; UserID=<username>;Password=<strong password>;Database=<site> _commerce
9. In the SQL Query text box, supply a valid SQL query. Note that the text box remains yellow and the OK button is unavailable until you enter a valid SQL statement.
   
   Two sample queries that return the required information for a mailing list are:
   
   
   • For the SQLOLEDB Provider:
     SELECT u_email_address AS rcp_email, g_user_id AS rcp_guid FROM retail_commerce.dbo.userobject WHERE u_email_address IS NOT NULL
   • For the SQL Server Driver:
     SELECT u_email_address AS rcp_email, g_user_id AS rcp_guid FROM UserObject WHERE u_email_address IS NOT NULL
10. Click OK. You are returned to the List Manager. You should see your mailing list, along with an indication of how many Recipients, Users, and E-Mails exist within the list. If you are returned to the List Manager and your list has a status of Pending, click List Manager to refresh the screen.
    
    NOTE: You can modify both queries to return the desired users for a direct mail campaign. To do this, specify a more defined WHERE clause.

If you receive an error during the creation of your mailing list, you must delete the current list and create a new list. There is no way in Business Desk to modify the entered list. When you create the new list, make sure that you correctly specify all the information needed to successfully create a mailing list.