Error message when you try to post Analytical Accounting transactions or save a Master record in Microsoft Dynamics GP: "Cannot insert duplicate key in object 'AAGXXXXX'"

To work around this problem, use one of the following methods below to compare the highest used value in the AA table (and sub-tables) against the next available value stored in the AAG00102 table. 

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

Method 1 - Use SQL Script to update values in AAG00102 for most AA tables

You can download a SQL script using the link below and run it in SQL Server Management Studio against the company database to automatically update the next available number stored in the AAG00102 table as compared to the last used value in the AA table. The script will look at the following tables: AAG10000, AAG20000, AAG30000, AAG00201, AAG00400, AAG00401, AAG00500, AAG00600, AAG00900 and AAG00903. So it looks at most of the AA tables, but not all, and not sub-tables for a series. To obtain the script, visit the following Microsoft Web site:

https://mbs.microsoft.com/Files/customer/GP/Downloads/ServicePacks/AA_Update_AAG00102.sql

Method 2 - Manual method to research and update value in AAG00102 for one AA table at a time

1. Open SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server version, and then click SQL Server Management Studio.
2. In the Connect to SQL Server window, log in to SQL Server Management Studio by using your sa password.
3. Click on the New Query icon to open a query window and copy or type the following script in the query window. Execute against the company database.

   select MAX (aaGLHdrID) from AAG30000
   */Insert in the appropriate column/table for the aaGLHdrID and AAG30000 placeholders in the script.

4. Note the query results which will show the highest value last used in the table. (If the table has sub-tables, make sure to check the highest last used value in those as well.)
5. Type the following script in the query window and execute against the Dynamics database:

   select * from AAG00102 where CMPANYID = 'nnn'

   Note In this query, replace the nnn placeholder with the company ID. To find the company ID, type the following query in the query window.

   select * from SY01500

   Select the DYNAMICS database in the list at the top of the window, and then press F5.
6. Verify that the value in the aaRowID field for the aaTableID value of 30000 (or appropriate table) is equal to or greater than the value that you noted in step 4.
7. Use the following script to update the value. To do this, type the script below in the query window, and then press F5.

   update aag00102 set aaROWID = 'yyyy' where aaTableID = 30000 and CMPANYID = 'zzz'

   Note In this script, replace the yyyy placeholder with the value that you noted in step 4. Replace the zzz placeholder with the company ID. Also replace the aaTableID with the appropriate table.