AA Primary Key SQL errors in GP 2015

(0) Share

Report

Posted on by Community Member Microsoft Employee

Since we've upgraded to GP 2015 from GP 2010, we are receiving random SQL errors when posting batches:

[Microsoft]SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PKAAG10001'. Cannot insert duplicate key in object 'dbo.AAG10001'. The duplicate key value is (488461,4).[Microsoft][SQL Server Native Client 11.0][SQL Server]Viol

I found this Microsoft article: https://support.microsoft.com/en-us/kb/897280 - I downloaded the script from Resolution Method 1 and applied to our Production databases, however, we are still getting these errors. Upon clicking OK on the error, the batch does actually post just fine - these errors appear to be more of an annoyance than anything, but SQL errors always concern me.

Rather than working around the issue as Microsoft suggests, does anyone know what may be actually causing these errors to pop-up in the first place?

*This post is locked for comments

All responses (6)

Answers (0)

RogerRogerATX 1,515 on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Aaron, I think GP will still let the GL transaction go through but I'm guessing that the AA details may not make it through to the AAG30k series tables.

This script should only need to be run once - assuming that you don't run the automated fix AA header Ids script from Microsoft. The work tables should keep incrementing upward but if you reset the headers GP will lower the #, which could cause a duplicate key error.

I wrote a custom email alert in SQL to let me know if there are orphaned transactions in the work tables so I can clean them out to prevent any issues - that's the only other time this can happen as far as I've run into (millions of AA transactions now); but we haven't had to reset the AA header ids since early on, after we fixed the.... "undocumented features" of AA.
Community Member Microsoft Employee on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Roger, thank you for your detailed response.

If I run this query one time, should that eliminate the error from coming up?.. Or is the query to be ran upon the error? Reason I ask is, when the error comes up, the user clicks "OK" and everything processes just fine
RogerRogerATX 1,515 on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Since the AAG tables follow this:

AAG10k - GL Work

AAG20k - Subledger - AR, AP, Fixed Assets (and header ID is used for AAG00400 series)

AAG30k - GL Open year

AAG40k - GL History Year

the AAG10k series is odd because if everything works right the MS script to reset headers will reset the # smaller - because those tables are supposed to be cleared out when the GL post completes. We ran into the exact same issue you did, and we added an alert that sends me an email every 12 hours looking for basically what are orphaned GL work transactions.

So this script, which needs to be run in this exact order to work properly, and make sure that no one is adding a JE at the same time you run it - it looks at the saved journal entries and allows those to stay...it's only the orphaned ones.

DELETE AAG10003 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)
DELETE AAG10002 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)
DELETE AAG10001 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)
DELETE AAG10001 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500)))
DELETE AAG10002 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500)))
DELETE AAG10003 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500)))
DELETE AAG10000 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500))
DELETE GL10001 WHERE JRNENTRY IN (SELECT JRNENTRY FROM GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500))
DELETE GL10000 WHERE BACHNUMB NOT IN (SELECT BACHNUMB FROM SY00500)
DELETE AAG10003 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))
DELETE AAG10002 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))
DELETE AAG10001 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))
DELETE AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000)

(of course offered without any warranties, and do a backup first)
Community Member Microsoft Employee on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Hello, thanks for your response. I ran the SELECT's you provided:

SELECT * FROM AAG10001 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000)) --Returns 51 rows with 2 unique aaGLWorkHdID

SELECT * FROM AAG10002 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000)) --Returns 51 rows with 2 unique aaGLWorkHdID

SELECT * FROM AAG10003 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000)) -- Returns 0 rows

SELECT * FROM AAG10001 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000) -- Returns 3 rows with 1 unique aaGLWorkHdID

SELECT * FROM AAG10002 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000) -- Returns 14 rows with 2 unique aaGLWorkHdID

SELECT * FROM AAG10003 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000) -- Returns 18 rows with 1 unique aaGLWorkHdID

I have noticed that none of the above tables (AAG10001, AAG10002, AAG10003, AAG10000) are not in the script provided from Microsoft. Do you have a script similar to Microsoft's that you can provide to fix my issue?

Thanks!
RogerRogerATX 1,515 on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Yes, I'm betting you have some orphan transactions out there in the work tables.

(I feel your pain, we actually broke GP because of our FA with AA entry - hit the max # out of the box for a mass retirement entry. Also, I had to rewrite MS script because at the time we started using FA with AA they hadn't updated the script to account for those tables)

Try these and see if you get any results:

SELECT * FROM AAG10001 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))

SELECT * FROM AAG10002 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))

SELECT * FROM AAG10003 WHERE aaGLWorkHdrID IN (SELECT aaGLWorkHdrID FROM AAG10000 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL10000))

SELECT * FROM AAG10001 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)

SELECT * FROM AAG10002 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)

SELECT * FROM AAG10003 WHERE aaGLWorkHdrID NOT IN (SELECT aaGLWorkHdrID FROM AAG10000)

I'm betting that you have an orphan transaction in those tables and the MS script doesn't look at the sub-tables for the header IDs...
Mick Egan 3,557 on at

Like (0)

Report

RE: AA Primary Key SQL errors in GP 2015

Aaron,

Not to feel alone, one of my customers using AA randomly receive a similar message when posting payables transactions, haven't found the cause as so random.

Mick