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!

Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC


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

  1. Anonymous says:

    >As always, you are a great help. Thanks.

  2. sheilajr says:

    >Mario – you are my savior. Thank you for posting this. It has saved me much time finding the duplicate. I was able to find the duplicate in two seconds. I too, can't fathom why they haven't come up with a easier way to do this.Sheila Jefferson-Ross

  3. >Hi Sheila,Glad to see you commenting on my articles. I will be at Convergence Atlanta 2011 – hope to see you in town again.MG.-

  4. Steve says:

    >Thanks… this saved me a lot of time and headache.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: