Today I created a new test company in my development environment and then restored the TWO database into my new TEST database. I dutifully ran the GP refresh company script and got a few strange errors, but eventually ran the script successfully.
But when I logged into GP and selected my new Test company, I received this error:
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.
I've never seen this error before, so I didn't know where to start. I Googled the message, and after digging around, I finally found a GP partner post that discussed the issue and mentioned that the problem was with the SY00100 table in the new company database. The record in that table should reference the Dynamics GP System database, not the Company database.
Sure enough, the SY00100 table in my new Test company had its own INTERID value of TEST, instead of the DYNAMICS system database. I corrected the value in SY00100 and was able to login.
Turns out that Tim Wappat documented this issue in detail a year ago, but I didn't see it in the Google search results.
Here is Tim's post from January 2015:
http://www.timwappat.info/post/2015/01/21/Automated-restore-of-Live-Company-to-Test-Company-in-GP2013-CHANGES
And I now see that Jen Kuntz also documented the issue in August 2015:
http://kuntzconsulting.ca/2015/08/restore-issue-on-gp-2015-drove-me-nuts/
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
From experience, this is usually caused by running an old version of the Copy Company scripts from Microsoft.
ReplyDeleteThe old one sets all INTERID to the db_name, but the new one handles the SY00100 table differently.
Thanks Ian, I agree, but I'm also wondering if there might be something that the script isn't catching. This is the first time I've had the issue, and it was on GP 2015. I initially used the script I had on hand, which gave me numerous strange errors.
ReplyDeleteI then tried the latest one from the MS KB article, and it worked, but then caused this error message.
So I'm not sure if using the older script first was the issue, or exactly how the incorrect system db value was set.
In my abundant free time, I may try another test.
Yes, running the old script first will have changed the entry from your system database to the company db name. The second script won't have changed it back as it has no way of knowing which is the system db.
ReplyDeletePlease check the resolution in the post below.
ReplyDeletehttp://www.njevity.com/blog/after-gp-company-database-restore-selected-company-not-configured-company-current-microsoft-dyn
SET NOCOUNT ON
ReplyDeleteCREATE TABLE ##COMPANIES
([DYNAMICS DB MASTER COMPANY ID] INT,
[COMPANY DATABASE COMPANY ID] INT NULL,
[COMPANY CODE] VARCHAR(50),
[COMPANY NAME] VARCHAR(50),
)
INSERT ##COMPANIES
SELECT [CMPANYID] 'DYNAMICS DB MASTER COMPANY ID'
, NULL
,[INTERID] 'COMPANY CODE'
,[CMPNYNAM] 'COMPANY NAME'
FROM [DYNAMICS].[dbo].[SY01500]
CREATE TABLE ##CCODES (ID INT identity(1,1), [COMPANY CODE] VARCHAR(50))
INSERT ##CCODES
SELECT LTRIM(RTRIM([COMPANY CODE])) FROM ##COMPANIES
DECLARE @NUM_CMPS INT
SELECT @NUM_CMPS = COUNT(1) FROM ##CCODES
DECLARE @COUNTER INT
SELECT @COUNTER = 1
DECLARE @CURRENTCOMPANYCODE VARCHAR(50)
DECLARE @GETSQLSTATEMENT VARCHAR(250)
DECLARE @CMPNYDBCOMPANYID INT
WHILE @COUNTER <= @NUM_CMPS
BEGIN
SELECT @CURRENTCOMPANYCODE = [COMPANY CODE] FROM ##CCODES WHERE ID=@COUNTER
SELECT @GETSQLSTATEMENT = 'SELECT CMPANYID CMPANYDBCMPANYID INTO ##CURRENTCOMPANY FROM ['+ @CURRENTCOMPANYCODE + '].dbo.[SY00100] GO'
EXEC (@GETSQLSTATEMENT)
SELECT @CMPNYDBCOMPANYID = CMPANYDBCMPANYID FROM ##CURRENTCOMPANY
UPDATE ##COMPANIES SET [COMPANY DATABASE COMPANY ID] = @CMPNYDBCOMPANYID WHERE [COMPANY CODE] = @CURRENTCOMPANYCODE
DROP TABLE ##CURRENTCOMPANY
SELECT @COUNTER = @COUNTER + 1
END
SELECT * FROM ##COMPANIES WHERE [DYNAMICS DB MASTER COMPANY ID] <> [COMPANY DATABASE COMPANY ID]
DROP TABLE ##COMPANIES
DROP TABLE ##CCODES