RSS

Navigation





Search the wiki
»

PoweredBy

itrezzo Technical Support Wiki

itrezzoAgent EPS and CCL SQL Connector



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 QueryOutlook Field
FirstNameFirst Name
LastNameLast Name
MiddleNameMiddle Name
GenerationSuffix
DisplayNameDisplay Name
Email1Email1
Email2Email2
Email3Email3
BusPhoneBusiness Phone
BusPhone2Business Phone2
BusFaxBusiness Fax
HomePhoneHome Phone
HomePhone2Home Phone2
HomeFaxHome Fax
OtherPhoneOther Phone
OtherFaxOther Fax
MobilePhoneMobile Phone
RadioPhoneRadio Phone (DCID)
PagerPager
JobTitleJobTitle
TitleTitle
CompanyCompany
DepartmentDepartment
OfficeOffice Location
BusStreetBusiness Street
BusCityBusiness City
BusStateOrProvinceBusiness State
BusPostalCodeBusiness Postal Code
BusCountryBusiness Country
HomeStreetHome Street
HomeCityHome City
HomeStateOrProvinceHome State
HomePostalCodeHome Postal Code
HomeCountryHome Country
SpouseSpouse
FileAsFileAs
NotesNotes
UserField1BlackBerry PIN
UserField2User Field 2
UserField3User Field 3
UserField4User Field 4
AssistantNameAssistant Name
AssistantPhoneAssistant Phone
InitialsInititials
UniqueIDDatabase primary key
WebPageWeb Page
Categories1Use a comma separated list to create Outlook categories
LastEditedDateDate stamp of last record modification
SQL Database Mapping

1Categories may only be set on a Custom Contact List. Note that any existing categories on a target contact are cleared and replaced with the supplied category list.

itrezzo Support Wiki version 3.0.5.619. See the itrezzo web site or the itrezzo blog site to learn about contact management on your smartphone.

The Trademark RIM® BlackBerry® Smartphone and BlackBerry® Enterprise Solution is owned by Research In Motion Limited and is registered in the United States and may be pending or registered in other countries. The owner of this wiki site is not endorsed, sponsored, affiliated with or otherwise authorized by Research In Motion Limited.

Microsoft Outlook, Microsoft Exchange Server, Microsoft Active Directory, Microsoft Windows Mobile are trademarks of Microsoft.