What if your GP Utilities upgrade fails on ASIEXP86 ?

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.

-- 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
  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
SELECT b.[ASI_Favorite_Dict_ID]
FROM ASIEXP86_bkp as b;

--DELETE ASIEXP86_bkp AS b; --> this can be uncommented if you want to delete the backup table
 -- 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 
 (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

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.


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑


The personal blog of Mark Polino

Erik Svensen - Blog about Power BI, Power Apps, Power Query

Blog about Power BI, Power Apps, Power Query

Freddys blog

Learn something new... - then Teach someone!

Life Hacks 365

Microsoft Dynamics & Microsoft Office Tips & Tricks

Vicky Rodgers - Microsoft Dynamics 365

Everything Microsoft Dynamics 365 for Customer Engagement


Stuff about the Microsoft Power Platform and Dynamics 365

The Dynamics Explorer

Exploring the World of Microsoft Dynamics ERP

Paul S. Randal

My contribution to the never ending Dynamics GP journey

%d bloggers like this: