CAUTION: This post is NOT intended for SQL Administrators who are not familiar with GP Table Structures. The following script may have serious implication on certain GP environments, so please use it at your own risk. This method is also not supported by Microsoft for obvious reasons.
This message was quite annoying when I created a Test Company in GP10 and restored a Production DB for R&D and Testing purpose.
Analytical Accounting windows didn’t get opened and was throwing the following error message:
There were several articles which cautioned / warned off from restoring only Company DB, which I am quite aware of. Because restoring another Company’s DB on to a Test Company will not have any issues unless it has got some products, like AA, depending heavily and real-time on DYNAMICS DB.
After a bit of R&D here and there, this is what I did. There is a table called AAG00104 in DYNAMICS Database, which is Analytical Accounting Company Setup table. I wrote an UPDATE on this table for the newly created company record as below:
SET aaCompanyStatus = 4, aaRelease = 10
WHERE CMPANYID = [Test Company ID Integer value]
This obviously worked in my environment and released the Test Company for AA usage.
As I mentioned at the start of this post, be cautious and do not venture into this unless you are sure of the consequences.
I am also not quite sure whether we have any fix for this issue, as I could not get one from CustomerSource or PartnerSource. I may have overlooked. Please do write a comment if we already have a script for this issue.
Hi Vaidy,I see you have experienced the fun of an AA database restore!I ran into something similar a few years ago where I was trying to (naively) restore a client's company database with AA onto my development server.Because there are key AA tables in the Dynamics DB, my AA records in the Dynamics DB did not match the client's AA data in their company database, so AA simply would not work and gave constant errors.The only reasonable way I found to deal with it was to always get a matching copy of the Dynamics database along with the backup of the company database.At the time, I read about one or two people that claimed to have figured out the scripts for updating the Dynamics DB AA tables to sync up with the company DB AA records, but I didn't have the time or desire to dig that deep.After that, I'm not a huge fan of AA.Thanks!Steve EndowDynamics GP Certified TrainerDynamics GP Certified Professional
Hi Steve,Oh yeah, quite exactly why I also literally scared of AA and it's behavior in our GP system. And trust me, this issue was the simplest issue that I could have got.There may be many other such issues, which quite fortunately I am not facing yet.Vaidy
I can use AA codes but I am unable to add new Trx codes..
Thank you for the script. This helped.