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.

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 helps and until next post, wish you happy reading.
@GP_Beat

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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.

Powered by WordPress.com.

Up ↑

SID-500.COM

Windows . PowerShell . Automation . Network Engineering . Cyber Security

JenKuntz.ca

Dynamics GP & other thoughts!

SergesPoint

Tricks and trips about SharePoint

Salim Adamon - Dynamics CRM Blog

Everything Dynamics CRM from a technical, architectural and business perspective

The Official Blog for Training Dynamo LLC

Microsoft Dynamics GP - Tips, Tricks, News and Thoughts

Dynamics GP Tips and Traps For Users and Developers

Talking about Great Plains, Dexterity, SQL, and some C#

smay772

A fine WordPress.com site

Tom Roush's Blog

Musings on Life, Lessons, and Laughter...

Belinda Allen, Microsoft MVP Business Solutions

Things I've learned from years of working with Microsoft Dynamics GP.

%d bloggers like this: