Paid Sales Transaction Removal error message

(0) Share

Report

Posted on by Carlos Cabrera

200

Hola Familia

When I try to do run a "Paid Sales Transaction Removal" against a specify customer (Microsoft Dynamics GP menu >> Tools >> Routines >> Sales >> Paid Transaction Removal) in Dynamics GP 10 with SQL 2005, I get the following error: [Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint ‘PKRM30101’. Cannot insert duplicate key in object 'dbo.RM30101'. I searched for the error message in Customer Source Knowledge base and found Article ID : 861084, which I followed and it showed the duplicate document (invoice) only when I ran the last select statement:

select a.TRXSORCE,a.RMDTYPAL,a.DOCNUMBR from RM20101 a, RM30301 b where

a.RMDTYPAL = b.RMDTYPAL and

a.DOCNUMBR = b.DOCNUMBR

But then I cannot figure out the next step, your help will be much appreciated.

Carlos

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (0)

Carlos Cabrera 200 on at

Like (0)

Report

Hola Familia
I also just noticed that when I do an Inquiry >> Sales >> Transaction by Customer and select the” HISTORY” option for the customer in question, I get the following error message.

[Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint ‘PK##2353639’. Cannot insert duplicate key in object 'dbo.##2353639'

Carlos

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (1)

Report

Carlos,

Take a look at my article Resolving SQL Duplicate Key Error Message When Executing RM Paid Transaction Removal. Hope this helps.

Was this reply helpful? Yes No
Carlos Cabrera 200 on at

Like (0)

Report

Hola Mariano

It worked, I was able to find the record in both the RM open and RM Historical tables. In my case I removed the record from the RM20101 and then I was able to run Paid Transaction Removal without any error.

Muchas gracias por todo.

Carlos Cabrera

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

Con todo gusto!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I ran the above query and tried other queries as well but could not find any duplicate record, however still getting the error. I am able to isolate the customer for which the routine is giving the error by running the routine for multiple sets of clients

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Zafar,

If you are unable to identify the record using the same query, you may have a problem in your RM Keys file RM00401 table.

The RM Keys file can be recreated by making a backup, deleting it, then running Check Links on the Sales files. If Check Links does not resolve the issue, it can be very useful to engage Microsoft support as they will help you identify what to remove, and in most cases right he actual query to run against the database (very helpful for risk-averse people).

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Harry, Thanks for your prompt reply.

Just to confirm that do you want me to drop the table RM00401 or delete all the records of RM00401.? Please also note that the error message is showing "...the duplicate key value is (9,PYMNT0260,49152)". As I understand, 9 is RMDTYPAL and PYMNT0260 is DOCNUMBR, however, the third value which should be the Customer ID does not exists in any column in the corresponding record in RM20101. In addition, I was able to run the routine successfully when I unchecked the check check box. In any case, I will run the checklinks on Sales tonight in a hope that maybe it will resolve the issue.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Zafar -

In cases where I have been able to identify the record causing the duplicate key violation, I have deleted the specific record from the RM00401 table, before running Check Links, and not deleted all the records from the table. In most cases errant data in the RM00401 table is the cause of the issue, so it is not surprising the data you've round doesn't match your expectations.

To be clear, I would

select * into RM00401_bak from RM00401

then

delete from RM00401 where DOCNUMBR = 'PYMNT0260'

then

run check links against the Sales files.

This should fix your problem. If it does not, I see a Technical Support incident in your future.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Harry

Thanks for your detailed reply. I deleted the recod in RM00401 and run the checklink and the record was created again. However I am getting the same error again. Can I just delete, after getting an approval from my account department" the record for PYMNT0260 in RM20101 and RM00401 as it is quite old record (year 2008)

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Zafar -

Sorry for the delay in my reply. In cases like this, sometimes there is no choice but to remove and errant record from the database. You will note, that was the solution implemented by the first poster. I would refresh your test database and test the fix there.

Refresh Test

Remove Transaction from Test

Run Check Links

Run Reconcile for the customer

Check inquiry for the customer

Once you're happy with how the fix behaves, then you can safely implement it in your production database.

Was this reply helpful? Yes No