Microsoft Dynamics AX Support

This blog contains posts by the Microsoft Dynamics AX Support Teams Worldwide

Dynamics AX 2009 Retail: Violation of PRIMARY KEY constraint while replicating an A-job

Topic: Error message in Retail Store Connect

When an A job is executed and it fails in the Retail Store Connect outgoingmessages table with the message “Violation of PRIMARY KEY constraint ‘PK_PRICEDISCTABLE’. Cannot insert
duplicate key in object ‘dbo.PRICEDISCTABLE'”.   Then it can be the issue described in this blog.

How to catch the issue:

In this case SQL profiler is a usefull tool to catch error messages. The SQl profiler trace was performed against the AXRETAILPOS database. When the Retail Store connect then contacts the POS database the statements are caught.

The statement that we caught in the trace

VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21)’,N’@P1
int,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 numeric(28,12),@P6
datetime2(7),@P7 datetime2(7),@P8 numeric(28,12),@P9 nvarchar(4000),@P10
numeric(28,12),@P11 numeric(28,12),@P12 int,@P13 numeric(28,12),@P14 int,@P15
nvarchar(4000),@P16 numeric(28,12),@P17 int,@P18 int,@P19 nvarchar(4000),@P20
00:00:00′,’1900-01-01 00:00:00′,5.000000000000,N’QAR’,0.000000000000,0.000000000000,1,0.000000000000,0,N’Pcs’,0.000000000000,0,2,N’00000014_091′,N’uat’,5637330589

User Error
Violation of PRIMARY KEY constraint ‘PK_PRICEDISCTABLE’. Cannot insert
duplicate key in object ‘dbo.PRICEDISCTABLE’.  


Verifying the data in the AXRETAILPOS database also comes back with the information that the record is already there



The issue was in reported support case that DELETE and INSERT was marked in the subjob. This has the effect on Retail Store Connect that it will try to INSERT the data instead of updating the record.




The correct setup is to only mark the ENABLED



Author: Kim Truelsen

Blog date: 9-2-2012