Deleting A Company Breaks SmartList

Microsoft Dynamics GPA 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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

1 thought on “Deleting A Company Breaks SmartList

Leave a Reply

Your email address will not be published. Required fields are marked *