Resolving SQL Duplicate Key Error Message When Executing RM Paid Transaction Removal

This is one of those issues that I cannot fathom why the Microsoft Dynamics GP development team has not address in its RM Paid Transaction Removal procedure. It seems when the RM module gets “out of sync” — usually after a crash of some sort — it will cause transaction records to post between the RM Open table (RM20101) and the RM History table (RM30101). Unfortunately, for the end-user the problem is only evident when executing the Paid Transaction Removal operation, since they are likely to experience the following error message:

(Microsoft)(SQL Native Client)(SQL Server)Cannot insert duplicate key row in object ‘dbo.RM30101’ with unique index ‘AK3RM30101’.

In addition, rebuilding the RM Key table (RM00401) does not solve the problem as the system will not know what to do with the same record found on both open and history tables. Given this situation, I have written the following query to identify and help in resolving the issue — the query must be executed against the company database:

SELECT a.rmdtypal, a.docnumbr, b.rmdtypal, b.docnumbr
FROM RM20101 a
  INNER JOIN RM30101 b ON (a.rmdtypal = b.rmdtypal) AND (a.docnumbr = b.docnumbr)

Once it is determined which document(s) is causing the problem, additional research will need to be conducted to establish which of the two records is the valid one and remove from either table (RM20101 or RM30101) accordingly. Establish if the document is fully applied and also check the Document Status (DCSTATUS) field in the RM Key table (RM00401) — 0: Reserved, 1: Work, 2: Open, 3: History.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/