SyncFilterManager CRM 2013 SP1 ORDER BY Generic SQL Error

Topics: Feature Request
Dec 17, 2014 at 5:31 PM
I've discovered an issue with CRM 2013 SP1 when using the SyncFilterManager for Server Side Synchronisation of Appointments, Contacts and Tasks.

CRM will write the items to Outlook/Exchange in the first SSS cycle, but on the subsequent cycles will return an Unhandled Exception error only surfaced by CRM trace logs. No record in the ExchangeSyncIdMapping views. Only a record in the TraceLog view with TraceCode 104
"Appointments, contacts, and tasks for the mailbox <name> couldn't be synchronized. The owner of the associated email server profile <name> has been notified. The system will try again later".

Fix as advised by Microsoft support:
Remove the <order attribute> node from the XML for that Sync Filter Template, then re-assign to the user.
This can only be done in a SQL statement to the SavedQueryBase table WHERE QueryType='131072' --Outlook Filter Template
Be careful as there are other system views in this table carrying a different QueryType value.

Deactivate, reactivate then Test/Enable Mailbox for that user.

This is apparently not san issue in CRM 2015 as the Sync Filters are managed through a new UI. But until then, and using our trusty SyncFilterManager, we have an issue.

Please will you verify this is a problem generally and, if so, perhaps a change to the SyncFilterManager to remove the <order attribute> node from the view prior to creating the Outlook Filter Template?

CRM trace logs:
Crm Exception: Message: Generic SQL error., ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException (0x80131904): ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func1 ExecuteMethod, IDbCommand command)
at Microsoft.Crm.CrmDbConnection.InternalExecuteScalar(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteScalar(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteScalar(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context)
at Microsoft.Crm.BusinessEntities.QueryProcessObject.ExecuteSyncQueryStage(EntityExpression entityExpression, String customSql, IList
1 parameterArray, String parentSyncEntryTableName, DatabaseQueryTarget target, ExecutionContext context)
at Microsoft.Crm.ObjectModel.SyncEntityBase.ExecuteSyncQueryStage(EntityExpression expression, StringBuilder sqlBuilder, DatabaseQueryTarget target, List`1 paramArray)
at Microsoft.Crm.ObjectModel.SyncEntityBase.ProcessOneFilterStage(SyncFilter filter)
at Microsoft.Crm.ObjectModel.SyncEntityBase.ProcessOneFilter(SyncFilter filter)
at Microsoft.Crm.ObjectModel.SyncEntityBase.ProcessEntity(SyncFilter filter)
at Microsoft.Crm.ObjectModel.SyncEntityBase.ProcessUserFilteredEntity()
at Microsoft.Crm.ObjectModel.SyncEntityBase.Process(BusinessEntityCollection filters)
at Microsoft.Crm.ObjectModel.SyncManagerStageModifiedOn.ProcessStageModifiedOn(SyncEntityBase entity, CounterList clEntity)
at Microsoft.Crm.ObjectModel.SyncManagerStageModifiedOn.PrepareSync()
at Microsoft.Crm.ObjectModel.SubscriptionService.PrepareSyncInternal(Guid subscriptionId, Int32 handlingType, ExecutionContext context)
at Microsoft.Crm.ObjectModel.SubscriptionClientsService.PrepareOutlookSync(Guid clientId, Int32 handlingType, ExecutionContext context)
ClientConnectionId:a3f5f5d7-07a8-45e6-b3b5-ca7f2dec0711
ultimately resulting in
Exchange sync worker for the mailbox is failed/terminated : {2ED12C97-B47C-E411-948E-005056971A1C}. Exception details : Unhandled Exception: Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncException: Failed to fetch the changes from the crm server : Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Generic SQL error.Detail:
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
<ErrorCode>-2147204784</ErrorCode>
<ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
<Message>Generic SQL error.</Message>
<Timestamp>2014-12-12T13:22:49.3916333Z</Timestamp>
<InnerFault>
<ErrorCode>-2147204784</ErrorCode>
<ErrorDetails xmlns:d3p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
<Message>Generic SQL error.</Message>
<Timestamp>2014-12-12T13:22:49.3916333Z</Timestamp>
<InnerFault>
  <ErrorCode>-2147220970</ErrorCode>
  <ErrorDetails xmlns:d4p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
  <Message>System.Data.SqlClient.SqlException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #E6E9842D</Message>
  <Timestamp>2014-12-12T13:22:49.4072333Z</Timestamp>
  <InnerFault i:nil="true" />
  <TraceText i:nil="true" />
</InnerFault>
<TraceText i:nil="true" />
</InnerFault>
<TraceText i:nil="true" />
</OrganizationServiceFault>
at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode)
at Microsoft.Crm.Extensibility.InprocessServiceProxy.ExecuteCore(OrganizationRequest request)
at Microsoft.Crm.Asynchronous.EmailConnector.CrmItemFinder.PrepareSync(ExchangeSyncState lastSyncState)
at Microsoft.Crm.Asynchronous.EmailConnector.CrmItemFinder.FetchChanges(IExchangeSyncErrorTable syncErrorCollection)
at Microsoft.Crm.Asynchronous.EmailConnector.CrmItemFinder.FetchChanges(IExchangeSyncErrorTable syncErrorCollection)
at Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncSteps.FetchDataStep.Execute()
at Microsoft.Crm.Asynchronous.EmailConnector.ExchangeSyncWorker.SynchronizeACTs()
.
Coordinator
Dec 17, 2014 at 6:39 PM
Hi,
I'm aware of this issue and the fix is already implemented in source code and in the latest release
Dec 17, 2014 at 7:10 PM
Fantastic Tanguy as always. Thank you. I will try it out with the toolbox 2014.12.2 version.