Most of you may be aware that during the initial GP setup with GP Utilities you define a 1-5 digit company ID / DB Name, but what most don’t know is how this can have deep implication in the global setup of your GP instance.
First and foremost the basics: the information about the company setup is stored in the system DB (commonly known as DYNAMICS, but that can be different)) in the table SY01500. This table holds all the important data that has to be used in GP, and one field in particular holds that same Company ID : INTERID. Now there is also another important field called CMPANYID that stores a sequential integer number starting with 1 for your very 1st company and increasing by each new company. The exception being the Demo company Fabrikam Inc (TWO) which receives a value of -1, possibly to get a different treatment when it comes to some system routine or validations. The sequence of numbers might get broken if you remove and add companies on a frequent basis, like creating several TEST companies and then deleting them.
Most of the company related data in GP is handled thru the CMPANYID since it’s a numeric integer value and quicker to compare. But some of the tables actually store the INTERID in the DYNAMICS DB and it might also happen that some other SQL objects like Stored Procedures (SPs) and Views could embed the Company ID, due to the way they were written. This can have catastrophic consequence or even break completely some processes.
Microsoft has a knowledge article (KB871973) that guides you thru the process of creating a new TEST company and restore the content of a LIVE company. As this company might carry on some of the identity values from the original company, a SQL script has to be run against the newly created TEST company to fix this.
Recently I came across a customer who had setup several companies for a complete new setup and knowing that each new company creation takes quite some time to run through, he wanted to have the 1st created company renamed to a different name, as it wasn’t matching the other companies pattern. Doing a quick search I stumbled over this old (2009) forum post on the community where someone had the same problem. A similar thread was left by the OP with keeping the original DB names after upgrade from Microsoft Small Business server setup, fearing that any changes could break something.
Following the 1st post I simply decided to change the name of the INTERID value in the SY01500 table to shorten the name and went on in SQL Studio Management (SSMS) to rename the company name. I then run the script from the KB871973 article to update all the tables in that DB. To make sure I hadn’t missed any entries, I ran a search against the DYNAMICS & Company DB to look for the full INTERID name. SQL didn’t return any on the Company DB, but did return 3 tables on the DYNAMICS DB: DB_Upgrade, SY02100 & SY40500. I ran a quick replace against those 3 tables and changed the values from the OLD to the NEW DB name.
So far so good. Off I went to fire up the GP client and try to logon to that renamed company.. Oups.. didn’t work.
Somehow GP was obviously still looking for the OLD value in some places.. but I didn’t knew where. First I thought about some other SQL objects (not tables, but SP’s or Views) that could have the INTERID value hard-coded somewhere, so I went to run the DMU (see more about that tool here).
After successfully applying the DMU process to both DB’s (DYNAMICS & Company), I tried to run GP again.. but still fails.
Then I thought about enabling the DEX SQL trace log at the start of GP, hoping it would go far enough to pick the error and write it into the blog. After analyzing the few lines from the DEX SQL trace log, I isolated some code that would return an error right after failing by clearly using the OLD company name, which brought me to write down the line of code into SSMS and run it to see the result.
There it was showing the OLD company name along with the new one and that field wasn’t picked up during my search in the DB, because the search string was only looking for whole strings, not combined content. I ran a general query against the DYNAMICS DB and the table SY02100 was initially identified, but only the field DBNAME was updated, not the DTAPTHNM field (no idea what this is supposed to be). I updated manually the rows (7 for each company) and that was it. My next GP client start to login into the renamed company was successful !
Word of Caution! This was on a GP setup with brand new companies, not holding currently any data inside of it, and with a very basic setup (no Manufacturing, no Project Accounting, no AA). It is fully possible that the more modules are involved, the more tables might hold references to the Company Name. I’d be cautious and check twice for strings matching the old company name. Granted, it might not be easy, being often only a few characters long (I’ve even seen single letter DB names!), it can be challenging to find in the tables. Start by searching strictly on the whole word only, which should return less than 5-6 tables in DYNAMICS. Then extend the search by including wildcards ‘%’, first at the end, then on both sides. This goes without saying the whole process shouldn’t take place before you’ve taken a full backup of the system & company DB’s.
Hope that you enjoyed reading this and until next post, wish you a happy life.
@GP_Beat
The Dynamics GP Geek Blog 
Just in case; under the word of caution, you might want to probably add that this must be done ONLY by someone who knows GP and not just any SQL expert. It gets even more interesting when ISV products are involved. Column names could be simply misleading at times and some might not even be company ID. Which also brings up the importance of naming convention standard when we create companies. 🙂
LikeLike
Hi Vaidhy,
This is correct, and I can’t emphasize enough that this isn’t for the fainted and should only be processed by some very knowledgeable GP consultant. As always, backups and testing is key !
LikeLike