Handling The Broken ClearCompanies Script

As I covered in this post there is a problem in the Clear Companies script available from Microsoft which breaks the User Smart List Master (ADH00100) table.

The issue is that the clear companies script deletes rows from the ADH00100 table when the relevant database doesn’t exist. This will only be a problem for sites which have SmartList objects created with SmartList Designer, which explains why I haven;t seen the problem more often.

I posted the code which can be used to update the clear companies script, but if you don;t want to maintaina custom version of this script, there are two actions you can take.

If you know of this issue in advance of running the clear companies script, you can build into your process the runing of a script against the table to change all of the CMPANYID entries in the table to the number of a company which does exist:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
UPDATE
	['User Smart List Master']
SET
	CMPANYID = (SELECT TOP 1 CMPANYID FROM SY01500 ORDER BY CMPANYID DESC)
FROM
	ADH00100 AS ['User Smart List Master']
WHERE
	CMPANYID <> 0
AND
	(SELECT COUNT(database_id) FROM sys.databases AS ['System Databases'] WHERE ['User Smart List Master'].CMPANYID = ['System Databases'].database_id) = 0
GO

This script is configured to only update the CMPANYID field when the relevant database doesn;t exist or the field is set to 0.

Continue reading “Handling The Broken ClearCompanies Script”

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) table.

Continue reading “Deleting A Company Breaks SmartList”