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'"
Note In this error message, AAG30000 is a placeholder for the table. The word "contraint" is a misspelling of "constraint."
The actual error message that you receive may contain one of the following tables:
For example, the '30000' table in the AAG00102 table will look at the AAG30000, AAG30001, AAG30002 and AAG30003 tables, so be careful to check all the tables in the series for the applicable 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 tablesYou 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:
Method 2 - Manual method to research and update value in AAG00102 for one AA table at a time
- 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.
- In the Connect to SQL Server window, log in to SQL Server Management Studio by using your sa password.
- 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.
- 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.)
- Type the following script in the query window and execute against the Dynamics database: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 AAG00102 where CMPANYID = 'nnn'Select the DYNAMICS database in the list at the top of the window, and then press F5.
select * from SY01500
- 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.
- Use the following script to update the value. To do this, type the script below in the query window, and then press F5.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.
update aag00102 set aaROWID = 'yyyy' where aaTableID = 30000 and CMPANYID = 'zzz'
Article ID: 897280 - Last Review: 10/22/2015 22:31:00 - Revision: 11.0
- kberrmsg kbmbsmigrate kbbug KB897280