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

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...-an-a-job.aspx
==============

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




Источник: http://blogs.msdn.com/b/axsupport/ar...-an-a-job.aspx