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:
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/
(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/
Comments
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
Glad to see you commenting on my articles. I will be at Convergence Atlanta 2011 - hope to see you in town again.
MG.-
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.
UPDATE SY40500 SET CMPANYID = WHERE CMPANYID = 0 AND DBNAME = ''
Glad this worked for you!
MG.-
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.
I am having the same issue for a specific customer, however, could not find any duplicates by running the above query and some other queries as well by trimming all the key columns from both tables and then making a join on all the three keys. Not getting any matching record when trying to join only document number from both tables RM20101 and RM30101.
Kindly advise what else can be done to resolve this issue.