Several years ago, I wrote about a restore issue "that drove me nuts" as the post title says. That was written shortly after the "named system database" functionality was introduced in Dynamics GP and a new company table SY00100 to capture the company ID and system database name.

What this post is about is a reminder that when creating a copy of another company, like a test company, or in my scenario, a temporary backup of a company, the SY00100 table needs to be updated. I'd forgotten until I logged into GP and got this message. In most of my "restore" scenarios I use scripts to automate updating things like this, but in a one-off situation, I didn't do that.

Error message, text of the message is in the paragraph after the image.
Error message when logging into this company in GP

For the benefit of screen readers, the text of this message is "The selected company is not configured as a company of the current Microsoft Dynamics GP system database. You must resolve the database configuration to log in to the company.". Skip past the background if you just want to see the scripts I used before and after to correct the issue.

Background

I was recently updating a client's DEV environment where we had prototyped some changes in the configuration of an ISV product that stores the configuration in the company database. To no lose the data from the proof of concept, I created a temporary new company with a backup of the data before refreshing the real test company with newer data. The proof of concept data was several months old so newer data was going to be useful, and being able to see/test the proof of concept would also be beneficial as we work through the final configuration change testing.

The SY00100 table

To be generic, the company I was copying was database "ABC", so I named the backup "ABC2". I created ABC2 in GP Utilities like normal. I backed up ABC, restored it over ABC2 in SQL, and briefly forgot about the SY00100 table until I ran into that error message above.

Here is what SQL looked like, the scripts are below the screenshot for those that want them.

  • A comparison of ABC and ABC2's SY00100 table using a "select * from" on both. Because one was a restore of the other, both were identical which is a problem for GP because the CompanyID does not match the company being logged into.
  • For the screenshot, I moved the select statement on SY01500 to the right-hand side. This is the company listing for a given GP system. It shows my "ABC2" company ID should be 16, not 13.
  • The rest of the screenshot just summarizes the steps I noted in the previous paragraphs.

The SQL scripts from the above screenshot are below. The ABC and ABC2 references should be changed to valid company databases in your environment. If your environment has a named system database, the "DYNAMICS" reference will also need to be updated to whatever that was called. 

select * from ABC..SY00100 -- replace "ABC" with your own company ID

select * from ABC2..SY00100 -- replace "ABC2" with your own company ID

select INTERID, CMPANYID, CMPNYNAM from DYNAMICS..SY01500
-- replace DYNAMICS with your system database if using a named system db

The SQL script to update the table is below. Again, the ABC2 should be changed to the company database in your environment, and the value to set the CMPANYID needs to be from your own SY01500 table in your system database. 

update ABC2..SY00100 SET CMPANYID = <CMPANYID from SY01500>
-- Update ABC2 to the company that needs the update
-- Look up the proper CMPANYID value from SY01500 in the system db

After updating that table, users should be able to log into the new company again. One of the many things GP is looking at during the company login process is that table to make sure the system database exists and is valid, and that the company ID matches the company being logged into.