Call Us: (301) 222-1038 eMail: info@logixts.com

Restoring a company database over test company generates error in Dynamics GP 2013

Scenario –

During the process of creating a test company that has a copy of live company data, the COMPANYID information and the INTERID information in the test company will match that of the live company. This information must be updated to reflect the correct information for the test company. The process to update the information is to execute the COMPANYID_INTERID.SQL script.

Cause –
The script changes a new table for Microsoft Dynamics GP 2013. The SY00100 table in the company database stores the system database name for example DYNAMICS. The script is incorrectly updating the SY00100 table to reflect the company ID thus causing the error. Running the following script against the test company database will reflect the incorrect data.

Select * from SY00100

Resolution –
The resolution is to discontinue the use of the outdated COMPANYID_INTERID .SQL script.  An updated script is available compatible with Microsoft Dynamics GP 2013. The updated script is below. To resolve the issue, run the script below against the test company.

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

About tkelebek