Description
When an Alternate Data Source is defined, for SQL Database, There are two requirements:
- Insure that itrezzo Service account has Read permissions on the database
- Setup a SQL query that can map fields to the correct values.
You can test these from the
itrezzo Administrator. The fields you can map into are shown in the table below. Once the query is working, expand
Alternate Contact Sources in the navigation tree and then select the specific data source you just created. The query will be executed and the first 1000 records in the right-hand contents window will be displayed.
A sample SQL query called
icam.sql is available for download.
Rules and mapping for Database Queries
To be valid a query must return one column named Email1 or a column named UniqueID. UniqueID is used only for an MCL based on the query that has the “Include no SMTP” flag set. Email1 will be used as the primary SMTP address unless it is actually a secondary proxy for an AD record – in that case Email1 will be changed to match the primary SMTP address found in AD.
A record will only be imported if it has a valid email address in the Email1 field, or a value for UniqueID. Records that don’t satisfy these criteria will be ignored.
Any other column returned by the query that corresponds to one of the fields defined by the agent will be added to that record, and pushed out target contacts according to this map:
| Column Name from Query | Outlook Field |
| FirstName | First Name |
| LastName | Last Name |
| MiddleName | Middle Name |
| Generation | Suffix |
| DisplayName | Display Name |
| Email1 | Email1 |
| Email2 | Email2 |
| Email3 | Email3 |
| BusPhone | Business Phone |
| BusPhone2 | Business Phone2 |
| BusFax | Business Fax |
| HomePhone | Home Phone |
| HomePhone2 | Home Phone2 |
| HomeFax | Home Fax |
| OtherPhone | Other Phone |
| OtherFax | Other Fax |
| MobilePhone | Mobile Phone |
| RadioPhone | Radio Phone (DCID) |
| Pager | Pager |
| Title | Title |
| Company | Company |
| Department | Department |
| Office | Office Location |
| BusStreet | Business Street |
| BusCity | Business City |
| BusStateOrProvince | Business State |
| BusPostalCode | Business Postal Code |
| BusCountry | Business Country |
| HomeStreet | Home Street |
| HomeCity | Home City |
| HomeStateOrProvince | Home State |
| HomePostalCode | Home Postal Code |
| HomeCountry | Home Country |
| Spouse | Spouse |
| FileAs | FileAs |
| Notes | Notes |
| UserField1 | User Field 1 |
| UserField2 | User Field 2 |
| UserField3 | User Field 3 |
| UserField4 | User Field 4 |
| WebPage | Web Page |
| UniqueID | Database primary key |
| LastEditedDate | Date stamp of last record modification |
SQL Database Mapping