You’re offline. This is a read only version of the page.
Announcements
135
Upon syncing the 1040 products job from AX to POS, the job is failing with the below error from event viewer. Any idea on the root cause and possible solution?
Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all write requests. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessWriteRequestException: Write request on table:[ax].[RETAILASSORTMENTLOOKUP] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Run() failed while performing write operation on table. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.PerformWriteOperationException: Query: MERGE [ax].[RETAILASSORTMENTLOOKUP] AS dst USING (SELECT [ASSORTMENTID],[CREATEDDATETIME],[LINETYPE],[PRODUCTID],[RECID],[VALIDFROM],[VALIDTO],[VARIANTID] FROM [#ax_RETAILASSORTMENTLOOKUP_89d28139-c661-4b6f-b548-8be38ea3e407]) AS src ON (dst.[RECID]=src.[RECID]) WHEN MATCHED THEN UPDATE SET [ASSORTMENTID]=src.[ASSORTMENTID],[CREATEDDATETIME]=src.[CREATEDDATETIME],[LINETYPE]=src.[LINETYPE],[PRODUCTID]=src.[PRODUCTID],[VALIDFROM]=src.[VALIDFROM],[VALIDTO]=src.[VALIDTO],[VARIANTID]=src.[VARIANTID] WHEN NOT MATCHED THEN INSERT ([ASSORTMENTID],[CREATEDDATETIME],[LINETYPE],[PRODUCTID],[RECID],[VALIDFROM],[VALIDTO],[VARIANTID]) VALUES (src.[ASSORTMENTID],src.[CREATEDDATETIME],src.[LINETYPE],src.[PRODUCTID],src.[RECID],src.[VALIDFROM],src.[VALIDTO],src.[VARIANTID]); DROP TABLE [#ax_RETAILASSORTMENTLOOKUP_89d28139-c661-4b6f-b548-8be38ea3e407]; ---> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'I_1305966847_-835757873'. Cannot insert duplicate key in object 'ax.RETAILASSORTMENTLOOKUP'. The duplicate key value is (5637144576, 5637239078, 0, 1). 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 asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection, SqlTransaction transaction) --- End of inner exception stack trace --- at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection, SqlTransaction transaction) at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction) --- End of inner exception stack trace --- 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) --- End of inner exception stack trace --- 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)
Hi Xusheng,
We managed to resolve it by deleting the records from ax.RETAILASSORTMENTLOOKUP and then scheduling a full sync from AX only for this table (by creating a new scheduler job).
Our only concern is how this issue happened and how we can avoid it in future. Any ideas on a probable root cause for such errors?
Hi,
Did you check your channel DB for table "ax.RETAILASSORTMENTLOOKUP'"?
This is the error message: Cannot insert duplicate key in object 'ax.RETAILASSORTMENTLOOKUP'. The duplicate key value is (5637144576, 5637239078, 0, 1).
I suggest you check AX DB for table "dbo.RETAILASSORTMENTLOOKUP" to see is there any duplicate record for it.
Another suggestion is log a service request to cooperate with Microsoft support team for it.
Hope this can help you. :)
Yes, this is the first time we are seeing this error. It is only affecting around 10 stores out of the 50+ stores.
AX 2012 version is 6.3.4000.2689 (kernel) and 6.3.3000.111(application).
Hi Vysakh,
refer below thread
community.dynamics.com/.../retail-synchronisation-issue-cannot-insert-duplicate-key-row
if you facing same issue even after above solution then try to re create /provision POS database and Run full sync.
Regards,
AP
Hi,
Can you share details of your AX2012 version?
Also, can you let us know if this is the first time you see this problem / error?
Many thanks,
Ludwig
#1
André Arnaud de Cal...
294,069
Super User 2025 Season 1
#2
Martin Dráb
232,858
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
