When you have Nolan Intercompany installed, there are a few additional things to consider before you run the script to update the company IDs. Per Nolan support: “We have triggers set up on the NCIC0003, NCIC1500, and NCIC3005 tables in the company database that prevent the MS script from updating those tables, which store the ICP trigger accounts, the templates, and the alternate accounts respectively. We do this to encourage people to clear out those tables when they’re copying live data into a test system so that we’re not accidentally posting test transactions back into a live database.”
To clear those tables, we ran a delete statement on the NCIC0003, NCIC1500, and NCIC3005 tables and then attempted to run the Test Company Fix statement (pasted below for convenience). We received an additional error on the NCIC5004, which is the table that holds old IC bank transfers. Since, per Nolan support, the data in this table is recorded in the GL and CM tables as well, we determined that we don’t need that data and can safely clear it in the new test company. Once we cleared that table, we were able to successfully run the script below and move on with our testing. You will want to review any additional tables that throw an error and determine how to handle each based on the data stored in the table(s).
Here’s the script we used to troubleshoot Nolan Intercompany update errors.
***Please note that if you plan to use Nolan Intercompany in your new test company, you will want to walk through the setup procedures and account mapping in the new company before you begin testing.***
Here is the script to run after restoring any existing db to a test company db in GP2013:
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
Have any questions? Please contact one of our Sikich experts at any time!
