System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column.

I had an issue today with the sync between Async Server and Client. Some of the jobs worked and some threw an error

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=XXXXXXXX;Initial Catalog=ClientChannelDB;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 datetime of the specified target column. —> System.FormatException: Failed to convert parameter value from a String to a DateTime. —> System.FormatException: String was not recognized as a valid DateTime.
   at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.Convert.ToDateTime(String value, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   — End of inner exception stack trace —
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   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)

The error turned out to be a mismatch in the configuration of the Channel Data Group where I had set the force locale to EN-UK instead of EN-US and apprently there is a difference in the date format. After changeing this parameter everything worked again.

Leave a Reply