Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

SQL Error

Posted on by Microsoft Employee

We are doing some testing on some additional software that links to GP. While trying to save an order, the tester is getting the following error, can someone help resolve this? Nobody within the company is super SQL savvy.

_____________________________________________________________________________________________________________________________________________________________________

Restoring a live company over a test company can lead to an SQL Exception: Violation of PRIMARY KEY constraint PKAAG20000. Cannot insert duplicate key in object dbo.AAG20000. when trying to save records such as sales documents.

There may be mismatched ID numbers in the AAGXXXXX tables in the DYNAMICS database that need to be corrected. Please contact Microsoft for specific advice on how to correct this problem.

Thank You,

Bryan

*This post is locked for comments

  • 26P2ER Profile Picture
    26P2ER 1,773 on at
    RE: SQL Error

    CyndiL:

    I am happy you didn't listen to that error :-)!

    I can't top Jim W's answer here but would like to add that  I have come across this error while running check links as well.

    Best of luck

  • Tim W Profile Picture
    Tim W 2,925 on at
    RE: SQL Error

    Analytical Accounting sure has some pain points.

    Using AA or not, you will often get this error and I only know how to solve via SQL so hopefully your team has query and update skills.  Others here might have a different approach. 

    There is a table in the DYNAMICS database (aag00102) that holds the next AA transaction number and you will find that the row for your test company (if there is a row) has a lower value than production's next number and when you are creating the transaction in test it pulls that lower number, tries to save the record, but as it already exists in the AAG table as you restored from production it then blows with the message you are getting. 

    We get this often when we are testing integrations on prod server against test company and as we know how to fix it it's mostly an annoyance,  probably we should script this fix as part of our restore process.  On test server where we restore both DYNAMICS and PROD it's not an issue.

    You need to identify your company id for both prod and for test.

    select * from DYNAMICS..sy01500

    Ours is 1 for PROD, 10 for TEST.

    Now query the aag00102 table

    select * from DYNAMICS..aag00102

    Ours looks like this:

    1256.Screen2.jpg

    Looking at PROD (1) and the 20000 row (row 8) next AA value is 508715

    Looking at TEST (10) and the 20000 row (row 11) next AA value is 500014

    Updating row 11 (dex_row_id 45) to 508715 will get around this error.  While here you might consider also updating the 30000 row as this miss match might interfere with posting in test.

    To update tis:

    update dynamics..aag00102 set aaRowID = 508715 where dex_row_id = 45

    Yours would depend on values in this table including updating the correct row.

    I've also seen this table not have any values for a company so in such case you would need to insert the row(s)

    insert into dynamics.dbo.aag00102 values (20000, 10, 508715)

    Hope this helps

    Tim  

       

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans