Tuesday, March 10, 2009

AA Error : "Cannot insert duplicate key in object"

If you are using Analytical Accounting and get the error which is something like "[Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint 'PKAAG20003.' Cannot insert duplicate key in Object 'AAG20003'."

The table name could be different.

Your first steps should be to check out KB article "897280" which has a script to fix this error. This should solve the problem 90% of the times.

However,  if you still get the error, this means the numbers are correct in the AAG00102 table and a duplicate key error is often caused by an invalid record in the AAG2000X  series of tables.

Run the following script against your company database -

select * from AAG20000 where aaSubLedgerHdrId = 0

select * from AAG20001 where aaSubLedgerHdrId = 0

select * from AAG20002 where aaSubLedgerHdrId = 0

select * from AAG20003 where aaSubLedgerHdrId = 0

If you get any results - those are the entries causing the problem. The 0 in the aaSubLedgerHdrId is not a valid record since the next number needs to come from the AAG00102 table. 

Hope that helps!


Anonymous said...

Does anyone have a copy of the script referenced in the article? It doesn't seem to be available on MS. Thanks!

Jivtesh Singh said...

Hi, I just checked and if you search in KB for 897280, you will find a KB article and you can download the script from there.

Anonymous said...

I cant seem to find the 897280 article anywhere, its not on the MS website unless Im missing something - can someone please point me in the right direction?


Jivtesh Singh said...

You'll find the article in the Dynamics Knowledge base in Customersouce. I just checked its still available.