Solved

Violation of PRIMARY KEY in SOP transaction in Great Plains Dynamics

Posted on 2012-04-03
16
1,191 Views
Last Modified: 2012-04-06
While posting a SOP transaction we received a Violation of PRIMARY KEY in SOP.  We have googled for the solution but the findings are overwhelming.  Even though we are going thru it bit by bit, we would appreciate EE advice on identifying this problem.
0
Comment
Question by:rayluvs
  • 10
  • 4
  • 2
16 Comments
 

Author Comment

by:rayluvs
ID: 37800851
Also when clicking Ok, we get a next error message:

DBMS 2627 Store Procedure aagCreateGLWorkSLRealTimePost return results.

The Violation of PRIMARY KEY is on PKAAG20000.  We assume this is Analytically Accounting and we do use it.

Some links that ate close, but not exactly to our problem are:
http://support.microsoft.com/kb/852808
http://support.microsoft.com/kb/861084
http://support.microsoft.com/kb/853548
http://community.dynamics.com/product/gp/f/32/p/67392/123077.aspx

Please advice.
0
 

Author Comment

by:rayluvs
ID: 37800896
Actual error message at Posting:


        [Microsoft][ODBC SQL Server Driver[]SQL Server]Violation of PRIMARY KEY
        constraint PKAAG20000. Cannot insert duplicate key in object AAG20000.


After Enter key:


         The stored procedure aagCreateGLWorkSLRealTimePost returned the following
         results: DBMS: 2627, Microsoft Dynamics GP: 2627
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 300 total points
ID: 37801327
Hi,

This error relates to Analytical Accounting, which you probably already deduced, and it's a pain.

Is this error occurring in your live / production database?  Or is it occurring in a test copy of your database or in a test environment that used restored databases?

If a test environment, one cause is that you restored a copy of a database and the AA index numbers in the DB do not match the AA index numbers in the Dynamics database.

I have tried to deal with a similar AA error in the past, but was unable to resolve it.  My workaround was to restore both the Dynamics database and company database that were backed up at the same time.  That was the only way to get both databases back in sync.

If it is occurring in your production database, I would recommend a support case with MS GP support.

But maybe another Expert has more experience troubleshooting AA issues.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 37801446
Thank you.  It is occurring in Live Production DB.  We have a lot of links that have many techniques to solve.  Hope another EE can advice.
0
 

Author Comment

by:rayluvs
ID: 37801449
Thanx for the info " AA index numbers in the Dynamics database", will also look into that.
0
 
LVL 18

Assisted Solution

by:Steve Endow
Steve Endow earned 300 total points
ID: 37801557
Hi,

What is unique to AA is that it stores it's transaction numbers in the Dynamics database rather than in each company database.  The problem occurs when the number in the Dynamics DB doesn't match the number sequence in the company DB.  I don't know what would cause it in a production environment, but it's not obvious how to fix it.

There is probably a support script or reconcile / check links that may fix it, but I would defer to MS support on the best approach.

So few people use AA that it is very difficult to get independent assistance with AA issues.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 

Author Comment

by:rayluvs
ID: 37802969
Beside Ms Support, can u point us to another direction ?
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 37803418
Hi,

Unfortunately I can't.  AA is used by so few people that I was unable to find any posts or KB articles that addressed the issue.

I think I read one post where someone indicated that he got scripts from MS Support to fix the situation, but when I asked him to post the scripts, I didn't get a response--it was probably an old thread.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:rayluvs
ID: 37803492
What about, based on your findings, how to understand or relate to AA tables to let say the SOP table.  What I mean, any references as to where to look regarding this relationship or understanding?
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 200 total points
ID: 37803540
Ramante, please find the next Number in AA (setup), and see if it's greater than the most recent AA trx number.
0
 

Author Comment

by:rayluvs
ID: 37803545
will do...
0
 

Author Comment

by:rayluvs
ID: 37803817
Trying to find it.  We looked at both:

Tools >> Setup >> Company >> Analyticall Accounting

and

Cards >> Financials >> Analyticall Accounting

In non there is an option where we can see the next Number in AA (which setup window?) or  most recent AA trx number (which window).

Please advice
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 300 total points
ID: 37803918
Hi,

The error you are receiving is not related to any AA document numbers and does not relate to AA setup.

The PKAAG20000 index (that is giving you the error) is for the aaSubLedgerHdrID field in AAG20000.  This is a numeric sequence number assigned internally by AA and tracked in AAG00102.

You can try getting the max value from AAG20000:

SELECT MAX(aaSubLedgerHdrID) FROM AAG20000

Then check the next value in DYNAMICS..AAG00102:

SELECT * FROM DYNAMICS..AAG00102 WHERE aaTableID = '20000'

This query of AAG00102 will list all companies, so you will need to figure out which record relates to your company (SELECT * FROM SY01500 to get the company ID values).

In theory, the AAG00102 record should be one number greater than your MAX in AAG20000.  If it is equal or lower, then you can try to manually increase it to the proper next number.

Just make sure you have good backups first and make sure that all users are out of GP so that no new AA transactions are being entered.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 200 total points
ID: 37803922
Find them in AAG00102 @ DYNAMICS DB.
0
 

Author Comment

by:rayluvs
ID: 37803940
Sendow, thank you, that is a good way to go from here.

Abdulmalek, I though it was a setup screen, but by what your saying, the its the table AAG00102 that we should be looking for "next Number in AA" "if it's greater than the most recent AA trx number"
0
 

Author Comment

by:rayluvs
ID: 37803944
Reading both entries, we think we may finally on the right track.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now