Retail sync job failed due to table field definition changed

A quick intro of the environments/installation:
1. Developed version of AX 2012 R3
2. Standard retail components (Eg. Channel database, Async server, Async client, Real-time service etc)

When running the sync job from Distribution Schedule, we came across this error - "System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.".

A quick guess would be the retail channel database is standard but pointing to a developed version of AX, some table field(s) might have been changed (Eg. Field length increased).

To find out which field length has changed/different between the two environment, the script below is used:

select S.tableName, S.name, S.max_length, D.max_length
  from                                                
      (select ST.name as tableName, SC.*              
         from ChannelDatabaseName.sys.tables ST       
        inner join ChannelDatabaseName.sys.columns SC 
           on ST.object_id = SC.object_id             
      ) AS S                                          
  join                                                
      (select DT.name as tableName, DC.*              
         from AxDatabaseName.sys.tables DT            
        inner join AxDatabaseName.sys.columns DC      
           on DT.object_id = DC.object_id             
      ) as D                                          
    on S.tableName   = D.tableName                    
   and S.name        = D.name                         
 where S.max_length != D.max_length                   

*NOTE: You might want to narrow down to just the tables in the failing sync scheduler jobs/subjobs by adding the table name into the query above.

This will give a list of the table and field names where the field length is different.

Once the list of table and field name is generated, then the field length is altered on the channel database.

Eg. ALTER TABLE [ax].[LOGISTICSPOSTALADDRESS] ALTER COLUMN COUNTY nvarchar(60)

Rerun the job and the error goes away.


Full stack trace

ApplySessionFileToClientDatabase encountered ProcessSourceRequestHeaderExceptionMicrosoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=ServerName;Initial Catalog=RetailDatabaseName;Integrated Security=True;Connect Timeout=60;Application Name="Commerce Data Exchange Async Client" ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
   at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
   at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.BulkCopyData(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
   --- End of inner exception stack trace ---
   at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
   at Microsoft.Dynamics.Retail.SynchClient.Core.DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)