Violation of PRIMARY KEY in SOP transaction in Great Plains Dynamics

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.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
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
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
Thanx for the info " AA index numbers in the Dynamics database", will also look into that.
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
 
rayluvsAuthor Commented:
Beside Ms Support, can u point us to another direction ?
0
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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
 
rayluvsAuthor Commented:
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
 
Abdulmalek_HamshoCommented:
Ramante, please find the next Number in AA (setup), and see if it's greater than the most recent AA trx number.
0
 
rayluvsAuthor Commented:
will do...
0
 
rayluvsAuthor Commented:
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
 
Steve EndowMicrosoft MVP - Dynamics GPCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Abdulmalek_HamshoCommented:
Find them in AAG00102 @ DYNAMICS DB.
0
 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
Reading both entries, we think we may finally on the right track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.