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 insertduplicate 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
execsp_executesql N'INSERT INTO "AXRETAILPOS".."PRICEDISCTABLE"("ITEMCODE","ACCOUNTCODE","ITEMRELATION","ACCOUNTRELATION","QUANTITYAMOUNT","FROMDATE","TODATE","AMOUNT","CURRENCY","PERCENT1","PERCENT2","SEARCHAGAIN","PRICEUNIT","RELATION","UNITID","MARKUP","ALLOCATEMARKUP","MODULE","INVENTDIMID","DATAAREAID","RECID")VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21)',N'@P1int,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 numeric(28,12),@P6datetime2(7),@P7 datetime2(7),@P8 numeric(28,12),@P9 nvarchar(4000),@P10numeric(28,12),@P11 numeric(28,12),@P12 int,@P13 numeric(28,12),@P14 int,@P15nvarchar(4000),@P16 numeric(28,12),@P17 int,@P18 int,@P19 nvarchar(4000),@P20nvarchar(4000),@P21bigint',0,0,N'00000006_088',N'10001',2.000000000000,'2011-12-2700: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 ErrorMessage Violation of PRIMARY KEY constraint 'PK_PRICEDISCTABLE'. Cannot insertduplicate 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