Aug 25, 2010

AA Transaction Error – Primary Key Voilation


There are quite a few errors comes in AA which are not highlighted in the knowledge base articles.
Transaction dimensions can't able to be saved. System had thrown you the following message
  • 'Primary key violation error on table AAG00600. Cannot insert duplicate records on table' along with that message there was another saying
  • 'The stored proc aagCreateTree returned the following results. DBMS: 2627, Great Plains: 0'.
Resolution
This problem is due to the aaTreeID which is the primary key violation of AAG00600. In order to rectify it follow the process below.
Run all the below steps in SQL Server Query Analyser
Step 1:- select * from dynamics..sy01500 (used to know the company id)
Step2:- select * from dynamics..aag00102 where aatableid = 600 ('600' is the source aa table ID for which the corresponding aaRowID mentioned is the last rowid inserted in AAG00600 for the corresponding is company ID)
Step3:- select * from two..aag00600 (look for the aaTree ID were the last record inserted is holding the same ID as that of the aaRowID in aag00102 table)
If the rowid and the tree id is not same for the corresponding company then you will be getting the above message which will not allow you to save the new transaction dimension for that company. So use the following code to make the rowid as same as that of the aatree id in AAG00600.
Step4:- update dynamics..aag00102 set aarowid = 14 where dex_row_id = 4

No comments: