How to Fix Table Errors When Updating Nolan Intercompany Company IDs

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!