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
exec
sp_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’@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
nvarchar(4000),@P21
bigint’,0,0,N’00000006_088′,N’10001′,2.000000000000,’2011-12-27
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
Message
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
CAUSE
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.
SOLUTION
The correct setup is to only mark the ENABLED
Author: Kim Truelsen
Blog date: 9-2-2012