Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
Deleting A Company Breaks SmartList
March 28th, 2018 by Ian Grieve
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 Comapnies 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) 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.