If you want the data for a transactional mailing populated from an iMIS SQL View, you must create the View from within an iMIS-integrated database and provide a name for that View.

Create a SQL View in iMIS

A SQL view can be created using more than one table and also any other views in your iMIS database using the proper SELECT statements. When creating your SELECT statement it is important to know that:

• COMPUTE and COMPUTE BY clauses cannot be used.
• An ORDER BY clause cannot be used unless there is also a TOP clause in the select list of the SELECT clause.
• References to a temporary table or table variable are not supported. Therefore, the INTO keyword cannot be used.

For detailed descriptions of other restrictions, refer to your Microsoft® SQL Server™ or MSDE™ documentation.

There are many advantages when using a SQL View as a Higher Logic Thrive Marketing Professional (Thrive Marketing Professional) target group:

• When creating a view, SQL Server™ validates your SELECT statement against the structure of your database to verify the existence of all database objects referenced in the view.
• SQL Server also validates the syntax of your SELECT statement and ensures that all rules for views are being followed.
• You can use any existing table or view from your iMIS database to create your view.
• Once the view has been created, it is easy to create a Thrive Marketing Professional target group that uses your view.

Once created, the name of the view appears under Select a SQL View in the Recipient Data File section of the Create Transactional Mailing dialog.

Rules for iMIS SQL Views

• Creating an iMIS SQL View requires a database administrator (DBA) or a consultant using the SQL Server Enterprise Manager. It is also based on the SQL Server and its views in the database. The view uses the SQL Server Enterprise Manager.
• The only column needed in the SELECT clause of your view is the NAME.ID. Any additional columns are ignored by Thrive Marketing Professional.
• If the column representing the iMIS ID is not named ID, use an AS clause to ensure that the column is named ID. Below are two examples:
• CREATE VIEW dbo.TestView1 AS SELECT ID FROM NAME WHERE STATUS = 'A'
• CREATE VIEW dbo.TestView2 AS SELECT BT_ID AS ID FROM TRANS WHERE TRANSACTION_DATE > '1/1/2015'
• Thrive Marketing Professional does not store the View name from iMIS; it stores the unique database ID associated with the View in the target_group_values field in Thrive Marketing Professional.
• Only distinct rows can be included in an iMIS SQL View.
• The Thrive Marketing Professional login used during the bridge-creation process must have SELECT permissions for any of SQL View to be used by Thrive Marketing Professional.

Learn more about iMIS SQL View formats and importing rules in iMIS Transactional Mailings Formats.