Thursday, June 5, 2008

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/

12 comments:

Anonymous said...

As always, you are a great help. Thanks.

sheilajr said...

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

Mariano Gomez said...

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.-

Steve said...

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

Anonymous said...

I also encounter that error now running on GP2010 only one customer cause this problem while the rest all ok. I also tried to run all most all of the sql from internet including the above, unfortunately i couldn't find any result. what i have notice is the error occur while it creating Stored procedures. please help me

Mariano Gomez said...

@Anonymous:
Create a DEXSQL.LOG while running paid transaction removal just for this one customer. Take a look at the DEXSQL.LOG file in notepad (scrolling to the end of it) prior to cancelling out of the error in GP. Post back with the lines leading up to the error.

अनीमेष said...

I encountered a scenario where DYNAMICS..SY40500 table was giving the same error message. To resolve the problem I had started the dexsql logging and found that the CMPANYID field on the table to correct CMPANYID as per the SY01500 table. Running SQL command something like this helped -
UPDATE SY40500 SET CMPANYID = WHERE CMPANYID = 0 AND DBNAME = ''

Chris V. said...

Awesome. Awesome, awesome, awesome. Thank you.

Mariano Gomez said...

Chris,

Glad this worked for you!

MG.-

McNulty said...

Mario - the script is great to identify the transaction causing the conflict, but what are the steps to resolve if the second document (which, in our case, occurred 2.5 years after the first document) is fully applied but is in an OPEN status in the RM20101 table?

Mariano Gomez said...

McNulty,

First and foremost, my name is Mariano ;-) No worries, I've been called worst.

Assuming you already conducted your research as I indicated in my article and have determined that the duplication exists, remove the document from the RM20101 with a simple SQL DELETE statement.

McNulty said...

Mariano - apologies for the typo- and thank you for your help on this. User is able to run the PTR process without errors.