Microsoft(SQL Server Native 10.0)(SQL Server)(Violation of Primary Key constraint PKRM00401. Cannot insert duplicate object 'dbo.RM00401

(0) Share

Report

Posted on by GuruDofGP

248

I have a client who got the RM batch posting interrupted while working remotely. The error message comes up as follows "Microsoft(SQL Server Native 10.0)(SQL Server)(Violation of Primary Key constraint PKRM00401. Cannot insert duplicate object 'dbo.RM00401. The duplicate key value is 9, 055010). I found the 96,000 line duplication in the RM00401 table, but the column name where they're at is "(No Column Name)". How do I delete these duplicates to get her batch recovered and unstuck please?

The stored procedure rmCLTransactionLogicalTableGrp returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0. - Error message when running CheckLinks.

Thank you for assistance in advance!

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (0)

GuruDofGP 248 on at

Like (0)

Report

The stored procedure rmCLTransactionLogicalTableGrp returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0. when running CheckLinks.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi GuruDofGP,

Check this article which will assist in locating the duplicate key.

blogs.msdn.microsoft.com/.../identifying-duplicate-transactions

Was this reply helpful? Yes No
Mick Egan 3,561 on at

Like (0)

Report

When running Check links, did you select "Receivables Open Transactions Files".

It could be the DCSTATUS has the incorrect Status in the RM00401.

You will also want to check the records don't appear in the Work and Open Tables already, which Check Links should fix.

As always make a backup and run in Test first before changes to tables.

Mick

Was this reply helpful? Yes No
GuruDofGP 248 on at

Like (0)

Report

I did run both Open and Historical Receivables Transactions when I ran CheckLinks. It didn't fix the issue. The Duplicate Key error is what comes up when trying to run CheckLinks.

I found the duplicate key but the 96000 lines of it are in a column called "No Column Name". I just need to know the SQL entry to delete them out and then release the stuck batch.

Please advise. Thank you

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report

It is not uncommon for a 'first time checklinks' to simply repeat the error when the RM00401 is involved. Based on the many conversations and cases in the community on this matter it appears that the challenge with the RM00401 Keys Master is that you often have to truncate that table first in order to rebuild it correctly using the checklinks.

When you truncate - as discussed in this link - community.dynamics.com/.../186000 - what can not be stressed enough is that this process really needs to happen with NO users in the system - only after a good backup of the databases and when you have enough time to complete without interruptions.

When completed this process will generate a very long list of errors - these are the RM Keys being added back to the RM00401 table - totally expected.

Be sure of backup - no one in system - be safe and post results.

Hope this helps. Post back often.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Please check your other tables - RM10301 and RM20101. Is there any duplicate invoice already posted which in this case would be found in both the tables. Depending on what you would like to do is to delete one of the records from either the work table or the open table. I think RM00401 is not letting you post because the record is already there. If, so find the invoice number from either from RM10301 and write query like Select * from RM00401 where docnumbr in (Select docnumber from RM20101 where bachnumb = 'Your_ batch') and this will give you the invoices in RM00401 which are there in RM20101. You can do the same for RM10301. In this way you would be very decisive what to do. You can delete records from RM00401 and when you run Checklinks, it would rebuild those records. Please make sure to check distribution and taxes. Take a backup of all the tables. I think in this case half of the invoices could have been posted and the user is trying to post the batch again. If that is the case, please delete the posted invoices from your work table/window. Please also be advised that open and work share distribution table, hence do not delete that.

Was this reply helpful? Yes No
GuruDofGP 248 on at

Like (0)

Report

Thank you for the link to truncate the RM00401 table, Bill! That definitely helps!

Do you know how I remove the 96000 duplicates in the "No Column Name" before truncating the table and running the CheckLinks again?

Thank you in advance!

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Guru, when you do the table truncate all the content of the table is removed.

WHne you complete the checklinks and after you review the 1,000's of lines that will be presented in error message - you should then look for the 9,600 duplicates - if they are there - post again.

Keep backup - keep safe practice and continue to post updates.

Was this reply helpful? Yes No
GuruDofGP 248 on at

Like (0)

Report

I ran the truncate table, and the CheckLinks twice, tried to recover the batch again, and tried to post the batch again. Still not didn't work. The cash receipts batch is still there stuck with "Posting Interrupted" on the batch recovery window, and the duplicate key lines in the amount of 96,000 of 055010 are still in the "No Name Column." Please help as soon as possible as this client needs to close out September now when the batch was initially entered.

Thank you in advance for all your assistance!

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Can you post a screen shot of the 96,000 of 055010 No Name Column. Try to get the column names and some of the data - do not need all of it of course, but a few lines before the duplicate starts would be good.

I am interested to know what is on the record line with that information.

To paste picture, use the Rich Formatting and then the Insert Media - one to the right of the CHAIN icon on the menu bar.

Was this reply helpful? Yes No