Analytical Accounting YE Close – AAG30000 Primary Key Violation Error

UPDATE (06/14/2011): A comment from Kumar clears this entire thing. There is a KB article which explains how to update Intercompany ID on a Test Company once we restore Live Data. Link to that KB article: Set up a test company that has a copy of live company data.

Our Finance Team and I decided to first do perform YE Close on our test company first before we make it to Live, to anticipate any possible issues and also learn this entire process as this our first YE close in GP2010.

You must by now anticipated what I had done to simulate Live Company. YES, I created a Test Company on same SQL Server (Mark, trust me, your post came after this exercise) and restored live company data on to Test Company.

This we normally do for any transaction level exercise that users would want to perform for their own sake.

The process started and after a good enough time of about 5 hours, it threw an error message, which said roughly Primary Key violation of key PKAAG30000 on table AAG30000.

Upon digging more and keeping a SQL Server profiler, I met this silly yet critical reason: AA was trying to create an entry on table AAG30000 in Live Company database. Why? I have no clue why it would ever happen.

My only guess is that every Journal Entry in GL table will have Live Company Database ID (such as TWO that denotes sample company). AA might have thought that this one is an Intercompany transaction and it requires to create an AA entry in Live Company for that portion of JRN that was processed in Test Company. The only field in AAG30000 that’s considered as Primary Key is aaGLHdrID. My live company would have gone past the next aaGLHdrID which is in Test Company (a back dated restoration) by the time I ran YE close on my Test Company.

I am yet to prove my own theory above, but I strongly doubt so.

Upon suspicion, I decided to create a TEST SERVER (Mark, your post had come exactly at that point of time). I just believe Mark Polino’s Weekly Dynamics article was so timely and I couldn’t agree for more.

After configuring a Test Server environment, I restored DYNAMICS and Company DB, pointed my GP to this test server and ran YE close as if I was running it on my production server.

Good god, YE Close got over without any AA error. I then had no other option than to believe that Test Company with Live Data do not augur well for exercises such as YE close and is in fact quite risky.

Addendum: With GP 12 Multi-Tenant architecture, we don’t have to worry about this, as we can easily setup a TEST SERVER along with LIVE SERVER in the same physical server.