While implementing at a retail customer we were stuck for a while with an occasional error in the data distribution of job 1150 (Catalog). Going through the looong error message it indicated that it couldn’t truncate a text field on the RetailPubCatalog table.
I’ve added the error message in the bottom of this post as reference.
When looking at the table definitions on the SQL server we quickly discovered that on the D365O table the ModifiedBy field on the RetailPubCatalog was a 20 characters field and on the Retail server side it was only 8 characters long.
Going through the content of the data package we saw why it wasn’t failing every time. Only some of the catalogs where modified by a user with more than 8 characters meaning that when only a given user was the last to modify the job failed.
The fix is simple. Run this on the retail server database to expand the field:
alter table [ax].RETAILPUBCATALOG alter column MODIFIEDBY nvarchar(20) not null
You can’t change the field size through the designer, so you need to script your way out of this.
Hopefully, this error will be fixed in a future update. Going through the database it didn’t have the ModifiedBy field on any other table than this.
Error message:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> Microsoft.Dynamics.Retail.CommerceDataExchange.ProcessDataPackageException: Error occurred when running SqlTargetRequestHandler. —> Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunException: Failed to merge data into table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO]) VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunException: Failed to run SqlWriteRequestRunner for table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO]) VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> Microsoft.Dynamics.Retail.CommerceDataExchange.PerformWriteOperationException: Error when writing data to table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO]) VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint ‘I_-1981983829_-162312163’. Cannot insert duplicate key in object ‘ax.RETAILPUBCATALOG’. The duplicate key value is (5637145329).The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection) — End of inner exception stack trace — at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection) at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.Run(SqlConnection connection) — End of inner exception stack trace — at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.Run(SqlConnection connection) at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunner.Run(SqlConnection connection) — End of inner exception stack trace — at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunner.Run(SqlConnection connection) at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection) at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessDataPackage(DataPackage dataPackage) — End of inner exception stack trace — at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessDataPackage(DataPackage dataPackage) at Dynamics.AX.Application.RetailCdxChannelDbDirectAccess.runDownload() in xppSource://Source/Foundation\AxClass_RetailCdxChannelDbDirectAccess.xpp:line 215 — End of inner exception stack trace —
