Microsoft Dynamics GP 2018 R2 and Year-End Upgrade - Known Upgrade Issues

With the release of Microsoft Dynamics GP 2018 R2, we are sharing any known issues that we run into with the upgrade of the new release so that you can be the most prepared for your upgrade. 

There is currently one known upgrade issue that you may encounter when upgrading to Microsoft Dynamics GP 2018 R2 or later:

You may encounter an issue with the ASIEXP86 table (SmartList Favorites Columns Master) when upgrading to Microsoft Dynamics GP 2018 R2 or later:
"The stored procedure SynchronizeTableData() of form duSQLAccountSynch : 27Pass Through SQL returned the following results: DBMS: 2601, Microsoft Dynamics GP: 0."

IF THE UPGRADE IS ALREADY IN THE FAILED STATE ON THE ASIEXP86 TABLE:
If you have already attempted to upgrade to Microsoft Dynamics GP 2018 R2 or later and have ran into an error on the ASIEXP86 table in the System Database, you can perform the following steps:

1. Make a copy of the ASIEXP86 table from the Dynamics / System database then delete the data from the table.
2. Run the upgrade/launch Dynamics Utilities.
3. Once the upgrade is complete, then delete the data in the ASIEXP86 in the Dynamics / System database again as the upgrade inserts default records.
4. Load the copied back up table from step 1 into the ASIEXP86 table.
5. Insert the new records for the new SmartList from 2018 R2 below script
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)

We will look to get this fixed in the January (2019) Hotfix for GP 2018, this is related to a new SmartList addition in GP 2018 R2 and favorites you have saved already.

With any upgrade, we recommend reviewing the known issues prior to the upgrade and we always recommended to perform a test upgrade to insure that you will have minimal downtime when it comes time for your production upgrade.

We currently have the following known upgrade issues for Microsoft Dynamics GP 2018 as of October 6^th, 2017:

1. Account Framework Table Conversion:  We convert several tables that have account framework information in them.  If the account framework tables in the company database, do not match the SY003001 and SY00302 tables in the DYNAMICS database, the upgrade will fail.  Please run the Account_Framework_Validation.txt script from the Upgrade Guide to validate your tables prior to the upgrade.  If results are returned, please work with your Partner or contact Technical Support to talk about the options for those tables.  We do have other account framework tables that can be changed but these are a few of the main tables we look at when it comes to account framework. Here are the tables we convert:

GL10110
GL10111
GL70500
GL70501
GL00100
GL00201
IV70500

2. Budget date records in Analytical Accounting: The AA Budget Tree Balance (AAG00904) table contains the records for each budget in Analytical Accounting. If there are budget date records in the AAG00904 table that do not exist in the aaDateSetup (AAG00500) table, the upgrade will fail and the following error occurs.

AAG00904 135 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column'YEAR1', table 'XXXX.dbo.AAG00904'; column does not allow nulls. UPDATE fails.

3. Database Compatibility:  If you are moving to a new SQL Server in addition to the upgrade to Microsoft Dynamics GP 2016, you can restore your databases to the new SQL Server and start your upgrade.  Please refer to KnowledgeBase Article 878449 for the steps to move to a new SQL Server.  Once the databases are restored to the new SQL Server, you must change the database compatibility. 

a. In the SQL Server Management Studio. right-click your database and click Properties.  
b. Under Select a Page on the left, click Options.
c. Change the Compatibility Level to match the version of SQL Server you are running SQL 2014 = 120, SQL 2016 = 130.

4. Purchase Order Tables: If there are detail records in the POP10110 and the POP30110 that do not have a matching header record in the POP10100 and the POP30100, the upgrade may fail on those tables.  Run the Invalid_Records_POTables.txt script from the Upgrade Guide to validate all detail records have a header record.  If results are returned, you can either remove the detail records or run checklinks in the current version you are running.

5. Server Drop Down List Blank: When launching Microsoft Dynamics GP 2018, the Server drop down list may be blank.  The Server drop down is the OBDC DSN that Microsoft Dynamics GP needs to connect to your SQL Server databases.  If the ODBC DSN is an older version or an x64 DSN, it will not show up in the list.  Please make sure you have a 32-bit ODBC DSN created using either the Native Client 10.0 or Native Client 11.0 driver.

6. Workflow Documents Must be Final Approved: If you use our Workflow for GL/PM/RM batches, Purchase Orders, Vendor Approvals, Credit Limit Overrides, Sales Quotes. Employee Onboarding, etc., all Workflow Documents must be final approved prior to the upgrade.  No documents can be pending.  The upgrade does check for this and will stop Utilities and provide a report showing what documents need to be approved.

7. Microsoft Dynamics Adapter Service: If you use the CRM Connector for Microsoft Dynamics GP and the Microsoft Dynamics Adapter Service is running on the server where you are performing the upgrade, please stop the service temporarily.  Once the upgrade is complete, the Microsoft Dynamics Adapter Service can be restarted. Note: CRM connector tool will not be supported (or be able to install) on GP 2018. Click Here for more information.

8. Professional Services Tools Library: If you receive an invalid version error on PSTL (Professional Service Tools Library), you will need an updated version of PSTL for GP 2018. Click Here for the PSTL Download.

9. Microsoft Dynamics GP Utilities Stops Responding: When you launch Utilities for the upgrade, most of the processing is done on the SQL Server.  If you happen to client away, Utilities may appear to stay white and show "not responding".  Please do not close out of Utilities, the upgrade is still running.  Please give the upgrade time to continue working.  If you feel the upgrade is hanging or locked up, please start a SQL Server Profiler trace to review activity.

10. Overlapping Fiscal Periods:  When you launch GP Utilities to start the upgrade, if you have overlapping fiscal periods, it will hang on the Multicurrency Setup Master Table.  Run the scripts below to determine if you have overlapping fiscal periods and correct the periods prior to starting the upgrade process.

-- Show overlapping Periods
SELECT distinct a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT 
FROM SY40100 a JOIN SY40100 b 
ON  a.PERIODID<>b.PERIODID 
AND a.PERIODDT <= b.PERIODDT 
AND a.PERDENDT >= b.PERIODDT 
AND (a.PERIODID <>0 AND b.PERIODID <>0) 
order by YEAR1, PERIODID

11. Payables Document Attach Error:  When you kick off the upgrade of a Company database, you may IMMEDIATELY encounter the following error if you have duplicate Attachment_ID values for a 'Payables Transaction' record and a 'Payables Transaction History' record in the CO00104 and CO00105 tables.:
The following SQL statement produced an error:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CO00104]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) update CO00104 set BusObjKey = replace(BusObjKey,'0\PM\Payables Transaction History','0\PM\Payables Transaction') where BusObjKey like '%0\PM\Payables Transaction History%'   

The error occurs when the upgrade attempts to update an BusObjKey value of  '0\PM\Payables Transaction History\XXXXXXXXXXX' to be '0\PM\Payables Transaction\XXXXXXXXXXX'. When it does this, the records with the same BusObjKey and Attachment_ID values, that already exist, creates a duplicate of the 'Payables Transaction' records, thus the Primary Key error that you will see in a Dexsql.log.

If you encounter this issue, you can download the following scripts (One for the CO00104 table and one for the CO00105 table): 

CO00104_Cleanup.sql

CO00105_Cleanup.sql

The scripts will compare the '0\PM\Payables Transaction\XXXXXXXXXXX' with '0\PM\Payables Transaction History\XXXXXXXXXXX' with values having the same Attachment_ID value, in the CO00104 and CO00105 tables. In the 'Results' tab it shows the duplicate records and then under the 'Messages' tab it gives the Delete script you can use to remove that duplicate record(s).

***NOTE: As always, ensure that you have full working back-ups of your DYNAMICS and Company databases before running any scripts.  The script identifies what records are going to cause the duplicate errors.  Then you can make the decision to use the Delete scripts to remove the duplicates to allow you to get around the Upgrade Issue.

12. EHW20200, EHW30200 and EHW40200 table errors: We have found an issue with the Dynamics GP 2018 upgrade code which causes the EHW20200, EHW30200 and EHW40200 tables to fail the upgrade if there is any data at all in these tables. These three tables are a part of the 'Employee Health and Wellness' module (PRODID 4955) which is one of the four modules that make up the Human Resources and Payroll Suite.

The issue is that when the upgrade attempts to put the data back into these three EHW tables, which both receive a new INACTIVE and EHW_Discount column added to them, it's trying to put the data back into the temp table where it already exists, instead of putting it into the newly upgraded table.

If you run into this upgrade error, you can resolve it by doing the following steps and scripts:

i. Make backups of the three EHW tables by running the following scripts against any company databases failing on these three tables:

a. Select * into EHW20200BAK from EHW20200

b. Select * into EHW30200BAK from EHW30200

c. Select * into EHW40200BAK from EHW40200

ii. Run these three scripts against the same company databases, to drop the original tables:

        a. Drop Table EHW20200

        b. Drop Table EHW30200

        c. Drop Table EHW40200

iii. Use this scripts to re-create the EHW20200, EHW30200 and EHW40200 tables at the Dynamics GP 2018 version to include the new INACTIVE and EHW_Discount columns. This script will also re-create the dexterity procedures for each table to recognize the new columns.

iv. Finally, run these scripts against each company database to put the data back into these two updated tables.

v. Make a backup of the company databases at this point.

vi. Lastly, run the following script that will update the system table to know that the EHW20200, EHW30200 and EHW40200 tables have already been 'upgraded', so you can then launch GP 2018 Utilities and continue with the rest of the upgrade:

Update DYNAMICS..DU000030 set Status = 0, errornum = 0 where PRODID = 4955 and fileNumber in (1, 2, 3) and Status = 135

Be sure to check back to Microsoft Dynamics GP 2018 R2 and Year-End Update Upgrade Blog Series Schedule page to review upcoming blog posts related to Upgrading Microsoft Dynamics GP and other helpful resource links.

Thanks,
 Karl Hendrickson | Support Engineer | Microsoft Dynamics GP