AnyView Creator upgrade (Any Version), Missing DDL values, Column Formats- MS provided SQL Scripts - KB #709
2 April 2014
After a Dynamics GP upgrade, and the following AnyView Creator upgrade, there may be missing DDL (drop-down-list) values, and missing column formats - In AnyView Creator Objects.
For example, the Quote,Order,Invoice... SOP Document types may show as numbers instead of the words, or columns that contain numeric data that have be reformatted to not show the default currency symbol or to modify the number of decimal places shown, may revert back to showing the default currency format defined by the GP database column for the source SQL table.
The cause for these issues is that some .SQL Scripts were run prior or during the GP upgrade process, which inappropriately removed records from AV* tables in the DYNAMICS database.
In the "Hot Topic" in the Microsoft Customer Source website entitled 'Upgrading to Microsoft Dynamics GP 10.0'; In the Critical Update Notices section, step 1 is to run a script required for the security conversion.upgrade steps for GP10.
Microsoft references and provides a SQL script called: MDGP10_SY01000SecurityConversionCheck.sql for this. Other KB articles may reference a different script: Company ID Cleanup.sql , that has similar code.
These SQL scripts contain code that Deletes Records All Tables in the DYNAMICS Database, based on the Values in any Tables column named "CMPANYID".
The scripts as written, will delete AnyView Smartlist Object column formatting and drop-down list value data.
The scripts iterate over all DYNAMICS database tables, regardless of the product to which they belong, and execute a delete statement that is qualified by a where clause which has the intent of removing records that reference "invalid" Company ID values.
However, Some AnyView tables use a different 'special value" in a COMPANYID column to represent "all companies", and so these records are valid, yet still get deleted by this .SQL Script. This causes formatting and drop-down list values to be lost in all AnyView Creator Smartlist Objects.
The DELETE statement is qualified by the WHERE clause:
...where CMPANYID not in (0,-32767)..
But AnyView stores its "all companies" token in the CMPANYID column as the positive value 32767.
So in order for the script to not delete AnyView table records that are needed, it should be modified before running, so that the clause reads:
...where CMPANYID not in (0,-32767, 32767)...
i.e. add the 32767 value to the exception list, everywhere that it occurs in the script.
Alternatively the script could be modified to exclude processing AV* and other third party tables.
If this unmodified .SQL Script is inadvertadly run before modifying it to correct for this flaw, rows will be deleted from at least the following 2 AnyView Creator tables:
If this has been done, these 2 tables should be restored from backup after the GP upgrade, in order to restore the formatting and drop-down list values for AnyView Creator Objects in the Dynamics GP system.
There is also another script that has been distributed by Microsoft, entitled:
Company ID Cleanup.sql
which is intended to cleanup DYNAMICS databases that have been moved to new SQL Servers with different sets of company databases present than the set of databases present where the DYNAMICS database originated.
This script has similar code that will delete AnyView Creator Smartlist Object formats and drop-down list values, requiring them to be manually restored as described above.