It’s been happening more and more often as I upgrade older GP versions (10, 2010 & 2013) to more recent builds like 2015 & 2018.. But it’s always a pain to handle those issues, especially as they are known for a while (since 2015 in fact) by Microsoft and never really fixed.. I had several e-mail exchanges with the support team and somehow it relates to the inability of the upgrade process to thoroughly validate all possible entries in the SmartList (SL) tables, especially when it comes to all the customized ones and some customers have customized hundreds of them from the default SL’s out of the box, and/or added more new ones with SL designer or builder.
In short, if you get this error screen during the GP Utilities run against your system DB (usually DYNAMICS), then there is a fix for this. You don’t have to restore your system DB and restart from scratch..
Microsoft tells you to modify your DEX.ini and run a DEX SQL trace against the GP Utilites so you can identify which records in the ASIEXP86 table are causing the issue, but that can be a lengthy process until you figured out everything that’s causing the problem.
I found a much simpler approach to tackle the problem by simply backup the ASIEXP86 table and truncate it before you continue the upgrade process. You could do all of this before you start all the process, but most of the time system admins just don’t know about that issue would come up.. Chances are that it will happen in over 50% of the upgrades. No worries, the fix can be applied even if your System DB upgrade is already in the failed state.
use DYNAMICS
-- Created by B. Bucher / GP Geek Inc. 2018-12-20
-- This is related to the 2018R2 upgrade bug affecting the smartlist favorites
-- It was tested against GP 2018 RTM, but should work equally on 2013 & 2015
--
-- This code is licensed under the Creative Commons
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
-- This query is provided "AS IS". No warranties expressed or implied.
-- Execute the steps one by one.. DO NOT RUN the whole script in one shot.
-- 1. Do a backup of the table before running GP utilities
IF OBJECT_ID('dbo.ASIEXP86_bkp', 'U') IS NOT NULL
DROP TABLE dbo.ASIEXP86_bkp;
select * into ASIEXP86_bkp from ASIEXP86
-- 1a. Make sure total rows match.
select count(ASI_Favorite_Name) from ASIEXP86
select count(ASI_Favorite_Name) from ASIEXP86_bkp
-- 2. now delete table content
Truncate table ASIEXP86 -- Stop after this step
Once you’ve executed all the steps from the above script one by one in the order, then you should be ready to start GP Utilities to upgrade your companies.
AFAIK the only major build that added new SmartLists to the default ones is GP 2018R2 release back in Oct. 2018 (build 18.00.0628). We’ll address those at the end after the upgrade is completed.
-- 3. Then Run the GP Utilities to upgrade to R2
-- 3a. Check # of rows after upgrade
select count(ASI_Favorite_Name) from ASIEXP86
select count(ASI_Favorite_Name) from ASIEXP86_bkp
-- 4. Delete table content a 2nd time after the successful upgrade
delete from ASIEXP86
-- 5. restore the data from the ASIEXP86_bkp
BEGIN TRANSACTION
INSERT ASIEXP86
([ASI_Favorite_Dict_ID]
,[ASI_Favorite_Type]
,[ASI_Favorite_Save_Level]
,[CMPANYID]
,[USRCLASS]
,[USERID]
,[ASI_Favorite_Name]
,[ASI_Field_Sequence]
,[ASI_Include_Column]
,[ASI_Display_Column]
,[ASI_Field_Number_Dict_ID]
,[ASI_Field_Number]
,[ASI_Column_Display_Name]
,[ASI_Display_Column_Width])
SELECT b.[ASI_Favorite_Dict_ID]
,b.[ASI_Favorite_Type]
,b.[ASI_Favorite_Save_Level]
,b.[CMPANYID]
,b.[USRCLASS]
,b.[USERID]
,b.[ASI_Favorite_Name]
,b.[ASI_Field_Sequence]
,b.[ASI_Include_Column]
,b.[ASI_Display_Column]
,b.[ASI_Field_Number_Dict_ID]
,b.[ASI_Field_Number]
,b.[ASI_Column_Display_Name]
,b.[ASI_Display_Column_Width]
FROM ASIEXP86_bkp as b;
--DELETE ASIEXP86_bkp AS b; --> this can be uncommented if you want to delete the backup table
COMMIT TRANSACTION
-- 5a. Check # of rows after restore of data
select count(ASI_Favorite_Name) from ASIEXP86
select count(ASI_Favorite_Name) from ASIEXP86_bkp
When your GP Utilities upgrade is completed, run the above code step by step. You’ll clear the newly populated ASIEXP86 table first before restoring all the data from your previous backup, and then compare the record count between the two to make sure you didn’t miss any. It’s up to you if you want to keep or delete the backup table.
Finally, if this is an upgrade to 2018R2, you’ll have to add some of the new SmartLists that have been added by Microsoft.
-- 6. Finally, try to Insert manually the new records added to 2018 R2
insert into ASIEXP86
values
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',1,1,1,0,22600,22600 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',2,1,1,0,22601,22601 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',3,1,1,0,22606,22606 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',4,1,1,0,22637,22637 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',5,1,1,0,22638,22638 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',6,1,1,0,22787,26767 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',7,1,1,0,22681,22681 ,0),
(0,6,1,0,' ',' ','Deposits on Unposted Sales Transactions* ',8,1,1,0,22683,22683 ,0)
-- Done ! Check your SmartLists in GP .
-- 6a. Drop backup table
DROP Table ASIEXP86_bkp
GO
This should get you fully covered for your existing SmartLists and you wont run into the troubles to restart your GP Upgrade due to the ASIEXP86 table failure.
There is more about known issues with the 2018R2 Upgrade, and you can read about it here if you run into other problems: https://community.dynamics.com/gp/b/dynamicsgp/posts/microsoft-dynamics-gp-2018-r2-and-year-end-upgrade-known-upgrade-issues
Hope that you enjoyed reading this and until next post, wish you a happy life.
@GP_Beat
The Dynamics GP Geek Blog 
Leave a Reply