iMIS Triggered Mailings

Sending a mailing can be triggered in Informz as a result of an actions occurring within the iMIS database. This iMIS Triggered Mailing feature has many applications, such as sending out welcome mailings to new members when they are added to iMIS or sending confirmation mailings after someone registers for an event.

Because of the many possible applications, some database programming is required to enable and use the Triggered Mailing feature. The procedure below explains how to enable this feature.

This requires extension "Remote Triggered Mailings" (62) to be enabled.

Steps

If you are using SQL Server 2005, please ensure that the SQL Server Browser Service is running on your database server.

Ad hoc remote queries also must be enabled. To do this on your database server, go to the SQL Server Surface Area Configuration tool and follow these steps:

  1. Click Surface Area Configuration for Features.
  2. In the new window, click Ad Hoc Remote Queries on the left.
  3. On the right, check the Enable OPENROWSET and OPENDATASOURCE support checkbox.
  4. Click OK. Close the Surface Area Configuration tool.

Details

Informz_Schedule_Triggered_Email uses the ID of the mailing you want to trigger to send the mailing to a particular iMIS Name ID. This stored procedure is called from whatever process triggers the email to go out (e.g., a form is completed on client’s web site, an order record is added to your iMIS database, a member registers for a particular event, etc.).

Syntax

EXEC informz.INFORMZ_SCHEDULE_TRIGGERED_EMAIL
@mailing_id,
@name_id,
@date_to_send,
@add_if_already_exists

Parameters

The stored procedure takes four parameters:

Parameter NameParameter TypeDescription
@mailing_idIntID of the mailing you want to trigger Informz to send. Your Advisor can walk you through where to retrieve this in the UI.
@name_idVarchar; length=10ID, from the iMIS Name table, of the person you want to send the email to.
@date_to_sendDatetimeThe exact date and time you want Informz to send the email to this person.
@add_if_already_existsBit;possible values: 0 or 1Indicates whether you want to schedule this email for this person if a record with that same @mailing_id and @name_id already exists, but hasn’t been scheduled in Informz yet. The default value for this parameter is zero (0), which indicates not to schedule the same email to this person multiple times.


Return Code Values: 0 (success), 1 (failure)

The most likely cause of an error is that the ID sent into the @name_id parameter doesn’t exist in the NAME table.


Result Sets: There are no returned from this stored procedure.

Enabling Ad Hoc Remote Queries

Ad hoc remote queries must be enabled for Informz to transfer data into user-defined tables. Below is the command line option for setting the values. Note that you must be connected to a SSMS query window as a sysadmin or as a user with serveradmin rights, and you must have the query window connected to your iMIS database.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

INFORMZ.INFORMZ_TRIGGERED_EMAILS 

This table is used by the Triggered Mailing feature to store the list of the Mailing IDs and Name IDs of emails waiting to be scheduled in Informz, emails that have already been scheduled successfully in Informz, and emails that were not scheduled in Informz because of an error.


The TRIGGER_STATUS field in this table indicates the status of the email. The possible values for the TRIGGER_STATUS field are as follows:

Trigger_StatusDescriptionResolution
0New record; hasn't been scheduled in Informz yet.No error occurred.
1In progress.In progress.
6Record successfully processed and scheduled in Informz.No error occurred.
100Mailing ID does not exist on brand.The mailing ID could not be found in the Informz account.  Verify that the Mailing ID is valid by checking in the UI as shown by your eMarketing Advisor.
101Mailing ID has been deleted from Informz.The mailing has been deleted in Informz.  To continue using this Mailing ID for a triggered email, undelete the mailing in Informz.
102Mailing ID not scheduled in Informz.The mailing was not scheduled as a triggered mailing in Informz, or the triggered mailing status was removed from the mailing.  Reset the mailing's trigger status.
103Subscriber not found in Informz.The Name ID could not be found in the Informz database.  This could be because the EMAIL field on the NAME record is empty or NULL, or the NAME.STATUS field contains a status not selected for this client's bridge install.
104Subscriber is unsubscribed from Informz.Informz attempted to schedule the mailing to the subscriber, but the subscriber was unsubscribed.  Therefore, Informz cannot send the email to this person.
105Subscriber not in target group at time of mailing.The subscriber was not listed as being in the target group when the mailing was published.

A process should be established whereby this table is checked periodically for records with a TRIGGER_STATUS >= 100. Once the error has been resolved (e.g. mailing has been scheduled correctly after receiving error 102), the TRIGGER_STATUS field of that row can be set to zero (0) to allow Informz to attempt to schedule the email again.

Note: If a record has TRIGGER_STATUS = 0 and the “date to send” has already passed, Informz processes the mailing immediately.

Trigger Me This

You are off to a great start! Once these features are enabled in SQL Server, please contact your Advisor to complete the configuration process for you to use this functionality.