Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Cannot insert duplicat...
Microsoft Dynamics GP (Archived)

Cannot insert duplicate key in object 'dbo.AAG20000'.

(0) ShareShare
ReportReport
Posted on by Community Member

I am trying to insert an invoice via eConnect and some custom code I wrote, but I keep getting the error "Violation of PRIMARY KEY constraint 'PKAAG20000'. Cannot insert duplicate key in object 'dbo.AAG20000'." After doing some research I came across kb 897280, "Error message when you try to post Analytical Accounting transactions in Microsoft Dynamics GP: "Cannot insert duplicate key in object".

This kb says that AAG20000 can be one of the tables as part of the error message. I tried Method 1 (running the sql they provide), but I still get the error. So I thought I would go through Method 2. However I get to step 3: Type the following query in the query window.
select MAX (aaGLHdrID) from aag30000


Select the Company database in the list at the top of the window, and then press F5.

My result from this is NULL. So I am guessing something is very wrong at this point, but I have no clue what this issue is caused from or how to fix it at this point, since I cannot find any more answers on a NULL value for aaGLHdrID in aag30000. Also to note, is that if I try to run select * from aag30000 I get now rows returned. This table is empty. Anyone have an idea how I can fix this without losing all of my data and rebuilding the db from scratch?

 Stanley

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Cannot insert duplicate key in object 'dbo.AAG20000'.

    Well this morning I noticed that the Analytical stuff was on the Admin page so I expanded the group and saw a setup link. So I ran through the setup and got an error on the PM tables, but the process finished. I then went back to try the same program where I was getting the above error and everything worked fine. I checked the AAG30000 table and found it to still be NULL. I am not sure what really changed, but we were able to continue.

     I am also unsure what the Analytical Accounting is used for at this point (note: i am a dev not an accountant). Thanks for your reply K Day. I have printed what you wrote for my future review. Once we move on to the point of needing or using the AA stuff I will check back to what you wrote.

     Stanley

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Cannot insert duplicate key in object 'dbo.AAG20000'.

     Are you sure that this company has been setup to use Analytical Accounting?  How about select * from AAG30000.  My knowledge of the AA tables is a little limited, but I always feel like the 10000 tables are work, unposted, 20000 tables are current year, or open, and the 30000 Tables are historical.  What is the setup of this database?  I hope this is a test system or at least a test company.

    I'll try to remember what I did to configure a test company to use AA.  I had to create a true company using GP and then do some of the basic setup.   Then I had to configure it to use analytical accounting.  Somewhere in DYNAMICS, it keeps the records of if AA is set up in a company.  At this point, you can restore a backup of a live company.  But if you don't do the AA configuration after creating the new test company, DYNAMICS does not know that it has Analytical accounting, but restored company has the AA tables.

    That script from Microsoft (if it the one I am thinking of) basically goes in and updates the "Next" number, I believe in DYNAMICS.  Say I have company A which is running Analytical accounting.  Say the next transaction number is 5000.  I then restore that company to test and then I only do 2 transactions, so that company's next transaction number is now 5002.  In the meantime, I have done 1000 transactions in the live company.  Then next time I restore that live company, DYNAMICS thinks the next number is 5002, so I try to do a transaction, it looks at DYNAMICS to get the next number, it comes back as 5002, then trys to insert that in the company database (which is over 6000 by now) and sees that there is already a transaction number 5002, then you get this error that you are getting, bucause I bet that this "Next Number" is a Primary key field.

     So anyway, every time I restore the live company to the test company, I have to run that AA script that I think you are looking at, along with the other script they recommend running that updates references to the old company name to wht is stored in the Company master.

    I hope this is somewhat helpful.  I actually had to get a support case going with Microsoft about this, and this is why I know a little bit about this error.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,403 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans