Dynamics CRM Outlook client: Synchronization Scenarios - Changes to items in Dynamics CRM (ACT)

About the series

This series will introduce you to two major areas related to Outlook Synchronization in Microsoft Dynamics CRM for Outlook:

Concepts & Rules

• Dynamics CRM Outlook client: Scenarios and Synchronization Rules

• Dynamics CRM Outlook client: Organization DB table structures - Server side

• Dynamics CRM Outlook client: Organization DB table structures - Client side

• Dynamics CRM Outlook client: User defined fields

• Dynamics CRM Outlook client: Settings that affect Synchronization & Tracking

Troubleshooting

• Dynamics CRM Outlook client: Highlights and Simple Methodology for troubleshooting

• Dynamics CRM Outlook client: Simple list of Troubleshooting Tools

• Dynamics CRM Outlook client: Synchronization Scenarios - Changes to items in Outlook (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Changes to items in Dynamics CRM (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Track an item in Outlook (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Merging overlapping changes

 

Introduction

In order to further understand the Outlook Synchronization, let’s look at some synchronization scenarios of the Outlook Synchronization. 

In this article, we will review the synchronization flow when an item (ACT – Appointment, Contact, Task) is changed in Dynamics CRM and how the changes are processed by the Outlook client and Dynamics CRM.

 

Changes to items in Dynamics CRM (ACT)

Synchronization flow:

(1) Dynamics CRM changes are fetched by calling the PrepareSync API on the server, which identifies the number of items that have changed for a particular client and returns that summary information.

(2) There are a variety of tables used within the CRM database during this process, such as:

Subscription table

SubscriptionClients

SubscriptionSyncInfo

SubscriptionManuallyTrackedObject

SyncEntry_<guid> table

(3) With this information, a GetSyncData call will be made against the server with a columnset that only contains the CRMID and the timestamp of the changed items, based on the XML returned by the prepare sync.

(4) This information is then added to the OutlookSyncTable table. 

The sync process will then process each record in the OutlookSyncTable table according to the change type and timestamp.

When one record is synced successfully, this entry will be removed and the IdMappingTable table entry will be inserted, updated or deleted.

If one entity fails and is not synchronized, then the corresponding entry in the OutlookSyncTable table will stay. 

On the next sync cycle, this entry will be synced again unless the user chooses to ignore the error.

(5) For the items retrieved in this way, the PostSync API will be called to identify to the server that the change on the item has been processed.

(6) Finally, the changes are merged, if necessary (more details about this scenario will be covered on the next article).

 

Troubleshooting sample

Let’s show a sample of how to use the troubleshooting tools (article Dynamics CRM Outlook client: Simple list of Troubleshooting Tools) considering this synchronization scenario: changes to items in Dynamics CRM (ACT).

 

Steps:

(1) Open CRM web client and navigate to Contacts section

(2) Navigate to CRM server, open the server side tools and clean up old traces:

SQL Profiler

 

(3) Still on the CRM server, open the SQL Server Management Studio (SSMS) and prepare the queries against the organization database:

select ContactId,FullName,JobTitle,ParentCustomerIdName as "Company Name"

from ContactBase

order by CreatedOn desc

 

select SubscriptionId, MachineName,LastSyncStartedOn, CompletedSyncStartedOn, SubscriptionType, SyncEntryTableName

from subscription

where systemuserid in (select SystemUserId from systemuserbase where DomainName = '<domain>\<user>')

               

select * from SyncEntry_<SubscriptionID>

where objectid = '<ContactId>'

 

Note:

• • SyncEntry_<SubscriptionID> = SyncEntryTableName from query 2.
  • <ContactId> = Contactid from query 1.

(4) Navigate to client machine, open the client side tools and clean up old traces:

Fiddler (enable decrypt HTTPS traffic, if necessary)

 

(5) Still on the client machine, open the SQL CE Toolbox tool > OutlookSyncCache.sdf database file and prepare the queries (attachment):

SELECT * FROM [IdMappingTable]; 

SELECT * FROM [OutlookSyncTable];

               

(6) From CRM web client, select a tracked contact record and change it by Explorer View (i.e. Company Name)

 

(7) Start the collections (server-side):

SQL Profiler

 

(8) Start the collections (client-side)

Fiddler (F12)

               

(9) From CRM for Outlook client, run the synchronization manually (Synchronize with CRM button)

(10) During the synchronization process, go to SQL CE Toolbox and run the queries, reviewing the outcomes

(11) After the synchronization process stop all collections

(12) Review the client-side collections:

(a) Review the Fiddler traces, looking for the following sample requests/responses

REQUEST

request [ i:type=c:PrepareOutlookSyncSubscriptionClientsRequest xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance xmlns:c=http://schemas.microsoft.com/crm/2011/Contracts ]

                               

RESPONSE

SyncInfoXml

<result><entity name="Contact" insert_count="1" /></result>

Note: insert_count could be insert or update action.

                               

REQUEST

request [ i:type=c:GetOutlookSyncDataSubscriptionClientsRequest xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance xmlns:c=http://schemas.microsoft.com/crm/2011/Contracts ]

                               

RESPONSE

SyncDataXml

<contacts EntityName="contact" MoreRecords="0" PagingCookie="" TotalRecordCount="-1" TotalRecordCountLimitExceeded="0"><BusinessEntities xmlns="http://schemas.microsoft.com/crm/2006/WebServices"><BusinessEntity xmlns:q1="http://schemas.microsoft.com/crm/2009/WebServices" xsi:type="q1:contact" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><q1:contactid>{A650EB13-1A08-E611-80DA-001DD8B877CB}</q1:contactid><q1:modifiedon date="4/22/2016" time="6:44 AM">2016-04-22T06:44:46-05:00</q1:modifiedon></BusinessEntity></BusinessEntities></contacts>

                               

REQUEST

request [ i:type=c:PostOutlookSyncSubscriptionClientsRequest xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance xmlns:c=http://schemas.microsoft.com/crm/2011/Contracts ]

               

RESPONSE

PostOutlookSyncSubscriptionClients

                               

REQUEST

request [ i:type=b:RetrieveMultipleRequest xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance ]

b:EntityName

Contact

                               

RESPONSE

ExecuteResult [ i:type=b:RetrieveMultipleResponse xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance ]

               

REQUEST

request [ i:type=b:UpdateRequest xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance ]

c:key

jobtitle

                               

RESPONSE

ExecuteResult [ i:type=b:UpdateResponse xmlns:b=http://schemas.microsoft.com/xrm/2011/Contracts xmlns:i=http://www.w3.org/2001/XMLSchema-instance ]

                               

(b) Go to SQL CE Toolbox and run the queries, reviewing the outcomes

(13) Review the server-side collections:

(a) Go to SSMS and run the queries, reviewing the outcomes.

(b) Review the SQL Profiler traces, looking for the synchronization, means PrepareSync and GetSyncData steps:

                                Search for SyncEntry

                                Creating temporary tables

create table #SyncEntry(ObjectId uniqueidentifier NOT NULL, ObjectTypeCode int NOT NULL, versionnumber binary(8), PRIMARY KEY (ObjectTypeCode, ObjectId))

                                                create statistics syncstat on #SyncEntry(ObjectTypeCode, ObjectId)

                                                create table #SyncEntryIds(

                                                                ObjectId uniqueidentifier NOT NULL,

                                                                PRIMARY KEY (ObjectId))

                                                create statistics syncstatStage on #SyncEntryIds(ObjectId)

                                                create table #SyncEntryIdsFiltered(

                                                                ObjectId uniqueidentifier NOT NULL,

                                                                versionnumber binary(8),

                                                                PRIMARY KEY (ObjectId))

                                                create statistics syncstatStage on #SyncEntryIdsFiltered(ObjectId)

                                                               

                                Fetching temporary tables with recent activity records:

                                                @ActivityTypeCode0=4251 - RecurringAppointmentMaster

                                                @ActivityTypeCode0=4201 - Appointment

                                                @ActivityTypeCode0=4214 - ServiceAppointment

                                                @ActivityTypeCode0=4212 - Task

                                                @ActivityTypeCode0=4210 - PhoneCall

                                                @ActivityTypeCode0=4207 - Letter

                                                @ActivityTypeCode0=4204 - Fax

                                                               

                                Fetching temporary tables with recent contact records:

                                                exec sp_executesql N'truncate table #SyncEntryIds;

                                                insert into #SyncEntryIds

select distinct ContactId from [Contact] WITH (NOLOCK) where VersionNumber > convert(timestamp,@VersionNumber0)

union

select ObjectId from PrincipalObjectAccess WITH (NOLOCK) where VersionNumber > convert(timestamp,@VersionNumber0) and ObjectTypeCode = @ObjectTypeCode0and PrincipalId in (select PrincipalId from SystemUserPrincipals WITH (NOLOCK) where SystemUserId = @SystemUserId0);

Select @@ROWCOUNT',N'@VersionNumber0 bigint,@SystemUserId0 uniqueidentifier,@ObjectTypeCode0 int',@VersionNumber0=405473,@SystemUserId0='49A682D6-97F6-E511-80D8-001DD8B877CB',@ObjectTypeCode0=2

                                                               

                                Fetching SyncEntryIdsFiltered, considering SubscriptionManuallyTrackedObject

exec sp_executesql N'insert into #SyncEntryIdsFiltered(ObjectId, versionnumber)

select distinct #SyncEntryIds.ObjectId, contact0.VersionNumber from #SyncEntryIds join Contact as contact0 (NOLOCK) on (contact0.ContactId = #SyncEntryIds.ObjectId)

where ("contact0".OwnerId = @OwnerId0 and "contact0".StateCode = @StateCode0 and "contact0".ContactId is not null) or [contact0].[ContactId] in (select ObjectId from SubscriptionManuallyTrackedObject as smt (NOLOCK)

where smt.SubscriptionId=''6c9eee1e-9df6-e511-80d8-001dd8b877cb'' and smt.ObjectTypeCode=2 and smt.Track=1 ); Select @@ROWCOUNT',N'@OwnerId0 uniqueidentifier,@StateCode0 int',@OwnerId0='49A682D6-97F6-E511-80D8-001DD8B877CB',@StateCode0=0

 

 Updating SyncEntry_### table with recent records

exec sp_executesql N'update syncentry set SyncState = 2 from SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb syncentry

where syncentry.ObjectTypeCode = @ObjectTypeCode0 and syncentry.ObjectId in

(Select ObjectId from #SyncEntryIds where ObjectId not in (Select ObjectId from #SyncEntryIdsFiltered));

Select @@ROWCOUNT',N'@ObjectTypeCode0 int',@ObjectTypeCode0=2

exec sp_executesql N'insert into SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb (ObjectId, ObjectTypeCode, VersionNumber, SyncState)

select ObjectId, @ObjectTypeCode0, versionnumber, 1 from #SyncEntryIdsFiltered

where ObjectId not in (select ObjectId from SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb where ObjectTypeCode = @ObjectTypeCode0);

Select @@ROWCOUNT',N'@ObjectTypeCode0 int',@ObjectTypeCode0=2

exec sp_executesql N'update syncentry set VersionNumber = #SyncEntryIdsFiltered.versionnumber, SyncState = 1 from SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb

syncentry join #SyncEntryIdsFiltered on (#SyncEntryIdsFiltered.ObjectId = syncentry.ObjectId and syncentry.ObjectTypeCode = @ObjectTypeCode0)  

where syncentry.VersionNumber <> #SyncEntryIdsFiltered.versionnumber;

Select @@ROWCOUNT',N'@ObjectTypeCode0 int',@ObjectTypeCode0=2

                               

                                Finishing process

truncate table #SyncEntryIds;

truncate table #SyncEntryIdsFiltered

drop table #SyncEntryIds;

drop table #SyncEntryIdsFiltered

select ObjectTypeCode, SyncState, count(*) as EntityCount from SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb

where SyncState <> 0 group by ObjectTypeCode, SyncState

                               

                                Updating SyncEntry_### table

exec sp_executesql N'select

"contact0".ContactId as "contactid"

, "contact0".ModifiedOn as "modifiedon"

from ContactBase as "contact0"

where

(("contact0".ContactId in (select Top 100 SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb.ObjectId From SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb where ObjectTypeCode = @ObjectTypeCode0 and SyncState = 1 order by SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb.ObjectId)))',N'@ObjectTypeCode0 int',@ObjectTypeCode0=2

exec sp_executesql N'update SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb set SyncState = 0 where ObjectTypeCode = @ObjectTypeCode0 and ObjectId in

(select TOP 100 ObjectId from SyncEntry_6c9eee1e9df6e51180d8001dd8b877cb where ObjectTypeCode = @ObjectTypeCode0 and SyncState = 1 order by ObjectId)',N'@ObjectTypeCode0 int',@ObjectTypeCode0=2

                                               

                                Search for "update [ContactBase]" and identify StartTime:

exec sp_executesql N'update [ContactBase] set [JobTitle]=@JobTitle0, [ModifiedOn]=@ModifiedOn0, [ModifiedBy]=@ModifiedBy0, [ModifiedOnBehalfBy]=NULL where ([ContactId] = @ContactId0)',N'@JobTitle0 ntext,@ModifiedOn0 datetime,@ModifiedBy0 uniqueidentifier,@ContactId0 uniqueidentifier',@JobTitle0=N'Owner',@ModifiedOn0='2016-04-22 12:21:21',@ModifiedBy0='49A682D6-97F6-E511-80D8-001DD8B877CB',@ContactId0='BEF25B79-D807-E611-80DA-001DD8B877CB'

                               

                                Noticed StartTime = 2016-04-22 07:21:21.520

                                Outlook change in Explorer: update statement is done after SyncEntry

                                Outlook change in Inspector: update statement is done before SyncEntry

References

Outlook Synchronization in Microsoft Dynamics CRM

Fiddler

SQL Server Compact & SQLite Toolbox