Microsoft Dynamics GP

Automated restore of Live Company to Test Company in GP2013 CHANGES

Tim Wappat

Jan 22, 2015 • 3 min read

Don’t be caught out by this change in the automated restore of test company database from live. This is something we do every Friday night on a scheduled SQL job, however since updating to GP2013 our test company is not working after the restore. Attempts to login are greeted by:

The selected company is not configured as a company of the current Microsoft Dynamics System Database

If you refer to my post Schedule restore of live company to test company Dynamics GP, then you will see that after the database is restored a script is ran to restore the company ID in the restored company database to update it to match that of the test company as registered in the DYNAMICS database.

A change has happened in GP2013, where the DYNAMICS database in GP can now be named anything you like, this has resulted in some changes to the way this script needs to work.

My first move was to refer to the original Microsoft KB that I got the information from the first time around, and yes there is a new script that forks depending on the presence of table SY00100. Check it out below, or better yet go to the KB itself, in case it has been updated since this post.

Set up a test company that has a copy of live company KB 872370

So the new script for GP2013 (and previous versions) is:

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) 
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3)) 
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

The old script for reference was:

/******************************************************************************/

/*    Description:    */

/*    Updates any table that contains a company ID or database name value    */

/*    with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table    */

/*    */

/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
  create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
  then 'update '+ a.TABLE_SCHEMA + '.' + a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
  'update '+ a.TABLE_SCHEMA + '.' + a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
  and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE' 
and a.TABLE_NAME <> 'UPR70501' and a.TABLE_NAME <> 'MPOSMAIN'
  set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
   insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
  Exec (@cStatement)
 
  FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables

NOTE: If you have already ran the old script, then the new script will now not execute, the error indicates that it can’t find company lookup table SY01500 in the company database. That is because it should be looking for it in the DYNAMCS database.

A look at the script reveals the issue, the old script wrongly updates table SY00100 to the test database name (it is just dumbly looking for instances of DBNAME), it should have DYNAMICS in the DBNAME field to make the new script work, but it has the test company DBNAME in there, due to this issue. Update it to be **whatever your DYNAMICS database name is,**using DYNAMICS as example below (your CMPANYID may be different).

Now run the new script again and it will run successfully and you can now login to the test company.

I have updated my previous post to point to this post and changed the script there to protect the innocent.