Deleting A Company Breaks SmartList

A client recently replicated their live system over to a standalone test system, but, when they did so, they did not migrate all of the databases over. They took all of the live and test over, but did not take the historical databases. After copying the databases over, they ran the Clear Companies script available in KB855361.

Everything looked fine, until they tried to open SmartList when they received an unhandled script exception error; I don’t have a screenshot of the error, but the text is reproduced below:

Microsoft Dynamics GP
Unhandled script exception:
Index 0 of local array is out of range in script 'ASI_Initialize_Explorer_Tree'. Script terminated.

I did some checking around (both reviewing data using SQL Profiler, but also searching online where I found this thread on the Dynamics Community forum.) and determined that the error is related to the User Smart List Master (ADH00100)User Smart List Master (ADH00100) table.

This problem will only affect sites who have SmartList objects created in SmartList Designer.

The clear companies script needs to be amended to not update the ADH00100 table to avoid this issue occurring. The section you need to change is below with the new code highlighted:

declare CMPANYID_Cleanup CURSOR for 
  select ''delete '' + o.name + '' where CMPANYID not in (0,-32767)
    and CMPANYID not in (select CMPANYID from SY01500)''
  from sysobjects o join syscolumns c on o.id = c.id and o.type = ''U''
  where c.name = ''CMPANYID'' and o.name <> ''SY01500'' and o.name <> ''ADH00100''
  order by o.name
open CMPANYID_Cleanup
fetch next from CMPANYID_Cleanup into @statement
while (@@fetch_status <> -1) begin
  exec (@statement)
  fetch next from CMPANYID_Cleanup into @statement
end
close CMPANYID_Cleanup
deallocate CMPANYID_Cleanup

I have given feedback to Microsoft via the KB article that the script needs to be updated.