Microsoft Dynamics AX 2012 Upgrade – Using the State Transfer Tool with AX 2009, you can potentially have duplicate records created within table Shadow_DirPartyRelationship

 

This specific issue occurs only when using the state transfer tool in AX 2009. The problem is as follows:

  • We create new records in the Shadow_DirPartyRelationship.
  • The unique index on this references RefRecId.
  • The RefRecId will become the RecId for DirPartyRelationship during the bulk copy in AX 2012.
  • We use the SystemSequences NextVal from DirPartyReleationship to create these new RefRecIds in method – DirUpgrade::createPartyRelationship, then sub method DirUpgrade::getNewPartyRelationshipRecId.
  • These RecIds are coming from the SystemSequences table in the environment used for the state transfer, and not production.
  • The issue is that DirPartyReleationship is an existing table in AX 2009, so we do not copy back from the state transfer environment the SystemSequences record for that – which makes sense as there could have been newer records in production since.
  • The problem then comes when there have been some new records created, e.g. customers, vendors. Some of the delta processing scripts would call these methods, mentioned above, to add in new records in Shadow_DirPartyRelationship.
  • As the NextVal in SystemSequences for DirPartyRelationship is lower in production, it will use the same numbers again for the RefRecId in Shadow_DirPartyRelationship.

This potential problem can occur with all versions of AX 2012.

Workaround

After running the state transfer in production, and before you continue with the upgrade you can run the following SQL statement to update the NextVal in table SystemSequences to fix this problem:

UPDATE SYSTEMSEQUENCES

SET NEXTVAL = (

SELECT max(RECID) + 1 AS NEXTVAL

FROM (

SELECT REFRECID AS RECID

FROM SHADOW_DIRPARTYRELATIONSHIP

UNION

SELECT RECID

FROM DIRPARTYRELATIONSHIP

) T1

)

WHERE TABID = (

SELECT TABLEID

FROM SQLDICTIONARY

WHERE NAME = ‘DIRPARTYRELATIONSHIP’

AND FIELDID = 0

)

You need to restart the AOS Service after running the update statement above.