Summary:

An issue may occur when using the Sage CRM Exchange integration to synchronise appointments or tasks. This error will arise on a single appointment or task, and prevent the synchronisation of subsequent items.

Symptoms:

A HTTP 500 response code will be logged on sending a HTTP POST to the CRMJ webapp. An example error from the Exchangesyncenginestacktrace.log showing an error when synching a task is shown below:

2014-10-23/17:13:39.582/BST [SyncEngine thread] ERROR com.sage.scrm.syncengine.exchange.engine.SynchronisationThread.run com.sage.scrm.syncengine.core.engine.error.SageSyncEngineException: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://CRM-SERVER/sdata/crmj/crmExchange/-/tasks/$syncResults?trackingId=2b68a34a-b1ad-48ec-b756-19cacc285f19 failed: 500
com.sage.scrm.syncengine.core.engine.error.SageSyncEngineException: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://CRM-SERVER/sdata/crmj/crmExchange/-/tasks/$syncResults?trackingId=2b68a34a-b1ad-48ec-b756-19cacc285f19 failed: 500
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationProcess.execute(SynchronisationProcess.java:202)
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationThread.synchroniseResourceOneWay(SynchronisationThread.java:296)
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationThread.synchroniseResourcesOneWay(SynchronisationThread.java:281)
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationThread.run(SynchronisationThread.java:191)
      at java.lang.Thread.run(Unknown Source)
Caused by: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://CRM-SERVER/sdata/crmj/crmExchange/-/tasks/$syncResults?trackingId=2b68a34a-b1ad-48ec-b756-19cacc285f19 failed: 500
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationProcess$SourceEndpointThread.run(SynchronisationProcess.java:296)
      at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
      at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      ... 1 more
Caused by: com.sage.scrm.syncengine.exchange.engine.model.error.SageSDataEndpointException: com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://CRM-SERVER/sdata/crmj/crmExchange/-/tasks/$syncResults?trackingId=2b68a34a-b1ad-48ec-b756-19cacc285f19 failed: 500
      at com.sage.scrm.syncengine.exchange.sdata.SCRMEndpoint.sendSyncTargetResponse(SCRMEndpoint.java:179)
      at com.sage.scrm.syncengine.exchange.engine.SynchronisationProcess$SourceEndpointThread.run(SynchronisationProcess.java:279)
      ... 6 more
Caused by: com.sage.crm.httpconsumer.error.SageHttpConsumerException: POST on http://CRM-SERVER/sdata/crmj/crmExchange/-/tasks/$syncResults?trackingId=2b68a34a-b1ad-48ec-b756-19cacc285f19 failed: 500
      at com.sage.crm.httpconsumer.HttpConsumer.doPostPut(HttpConsumer.java:981)
      at com.sage.crm.httpconsumer.HttpConsumer.doPost(HttpConsumer.java:776)
      at com.sage.crm.httpconsumer.HttpConsumer.doPost(HttpConsumer.java:748)
      at com.sage.scrm.syncengine.exchange.sdata.SCRMEndpoint.sendSyncTargetResponse(SCRMEndpoint.java:172)
      ... 7 more 

Should the issue affect appointments, a similar error referencing a HTTP POST to the CRMJ webapp will appear, this time referencing appointments.

Cause:

The issue is caused by one of two issues:

  1. The hard deletion of task or appointment records from the Communications table, where those records have been synchronised with Exchange. This issue is caused by the unsupported manipulation of Communication records in the system.
  2. The presence of sync resources with duplicate UUIDs on the EcngSyncResource table.

Resolution:

The issues on the EcngSyncResoure table can be readily resolved.

1: Identify whether there are any unlinked synch resources, for which the corresponding Communication has been deleted. This can be done by running the following SQL statement:

select * from EcngSyncResource with (nolock)
where exsr_LocalEntityMappingID in (
        select exem_EcngLocalEntityMappingID
        from EcngLocalEntityMapping
        where exem_EntityName in ('appointment', 'task'))
    and exsr_LocalEntityID not in (
        select comm_communicationid from vTaskExchange with (nolock)
        union all
        select comm_communicationid from vAppointmentExchange with (nolock)
) 

If any sync resources are returned by the above, then they may be hard deleted from the EcngSyncResource table.

2: Identify whether there are any records with duplicate UUIDs on the EcngSyncResource table.

select exsr_uuid, count(exsr_uuid) as 'Number of UUIDs'
from ecngsyncresource with (nolock)
where exsr_LocalEntityMappingID in (
    select exem_EcngLocalEntityMappingID
    from EcngLocalEntityMapping
    where exem_EntityName in ('appointment', 'task'))
group by exsr_uuid
having count(exsr_uuid) > 1

If the above statement returns any records, then at least one sync resoure with a duplicate UUID will need to be deleted. It is suggested that the earlier record be hard deleted from the EcngSyncResource table.

More information:

This issue is caused by the presence of bad data in the Sage CRM database. It is suggested that after identifying and resolving this issue, an effort be made to identify the potential sources of hard deletes on the Communication table.