web
You’re offline. This is a read only version of the page.
close
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

I have the same question (0)
  • K Day Profile Picture
    K Day 7,365 on at

     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.

  • Community Member Profile Picture
    Community Member on at

    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

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans