Microsoft Dynamics GP and Year-End Update - Known Upgrade Issues

Hello everyone - we will be covering some known upgrade issues with Microsoft Dynamics GP and Year-End update and workarounds available for these issues.

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

The following is the list of known upgrade issues for Microsoft Dynamics GP and Year-End Update.

ASIEXP86 Table

During an upgrade to Microsoft GP 2018 R2 or later, you may have encounter an error regarding the ASIEXP86 table.
To workaround this issue you can perform the following steps:

1. Make a copy of the ASIEXP86 table from the Dynamics system database, then delete all the records from the table.
   
   
2. Launch GP Utilities and re-run the upgrade.
   
   
3. Once the upgrade is complete, delete all the records from the ASIEXP86 table as the upgrade process inserts canned records.
   
   
4. Restore the records back into the ASIEXP86 table from the copy created in step 1.
   
   
5. Insert the canned records using the script below:
   
   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)

This issue should be fixed with the release of the Microsoft GP 2018 R2 January 2019 hotfix.

Account Framework Table Conversion

We convert several tables that have account framework information stored in them. If the the account framework tables in the company database do not match the SY003001 and SY00302 table in the Dynamics system database, the upgrade will fail.

Please run the Account_Framework Validation 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 discuss options for those tables. We do have other account framework tables that can be changed, but these are a few of the main tables checked when it comes to account framework.

• GL10110
• GL10111
• GL70500
• GL70501
• GL00100
• GL00201
• IV70500

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 x.x][SQL Server] Cannot insert the value NULL into column 'YEAR1', table 'xxxx.dbo.AAG00904'; column does not allow nulls. Update fails.

Database Compatibility

If you are migrating to a new SQL server in addition to the upgrade, you can restore your database to the new SQL server and start your upgrade. Please refer to KB 878449 for the steps covering migration to a new SQL server.

Once the database are restored to the new SQL server, you must change the database compatibility.

1. In SQL Server Management Studio, right-click your database and click properties.
   
   
2. Select Options on the left side list.
   
   
3. Change the compatibility level to match the version of SQL server you are running.

Purchase Order Tables

If there are detail records in the POP10110 and POP30110 that do not have a matching header record in the POP10100 and POP30100, the upgrade may fail on those tables.

Run the Invalid_Records_POTables script from the upgrade guide to validate all detail records have a matching header record. If results are returned, you can either delete the detail records or run checklinks in the current version of Dynamics GP you are running.

Server Drop Down List Blank

When launching Microsoft Dynamics GP, the server drop down list may be blank. The server drop down is the ODBC DSN that is required by Dynamics GP in able to connect to your SQL server databases.

If the ODBC DSN is an older version or a 64-bit DSN, it will not appear in the list. Please make sure you have a 32-bit ODBC DSN created using Native Client 10.0 or newer driver.

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 On-boarding, 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.

Professional Service 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; you can download PSTL from here.

Microsoft Dynamics GP Utilities Stops Responding

When you launch GP Utilities for the upgrade, most of the processing is performed on the SQL server.

If you happen to take focus away from GP Utilities, it may appear to stay white and show "not responding". Please do not close out of Utilities; the upgrade is still running - 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.

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 script below to determine if you have an overlapping fiscal periods and correct the periods prior to starting the upgrade process.

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

Payables Document Attach Error

If you have duplicate Attachment_ID values for Payables Transaction record and a Payables Transaction History record in the CO00104 and CO00105 table you may immediately ecounter an error.

The following statement during the upgrade causes this issue:

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\XXXXXXXXXX" to be "0\PM\Payables Transaction\XXXXXXXXXX". When it does this, the records with the same BusObjKey and Attachment_ID values can result in a duplicate records causing a primary key error that is visiable in the DEXSQL.LOG.

You can run the following script to workaround this issue:

• CO00104_cleanup.sql
• CO00105_cleanup.sql

The scripts will compare the BusObjKey and Attachement_ID for any potential issues and will create a cleanup script in the result tab.

Note: As always, ensure that you have a full working backups of your system and company databases before running the generated cleanup script. The script identifies what records are potential going to cause a duplicate error - you can then make a decision to use the generated script to remove these records.

EHW20200, EHW30200 and EHW40200 Table Errors

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

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

If you run into this upgrade error you can get around this by running the steps below:

1. Make backups of the three EHW tables by running the following scripts against any company databases failing the upgrade:
   
   SELECT * INTO EHW20200BAK FROM EHW20200
   SELECT * INTO EHW30200BAK FROM EHW30200
   SELECT * INTO EHW40200BAK FROM EHW40200
   
   
2. Run the following scripts to drop the original tables.
   
   DROP TABLE EHW20200
   DROP TABLE EHW30200
   DROP TABLE EHW40200 
   
   
3. Use the script to re-create the three EHW tables at the correct 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.
   
   
4. Run these scripts against any company failing the upgrade to restore the data back from backups created in step 1.
   
   
5. Run the following script that will update the system table to mark the three EHW tables upgraded.
   
   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 our Microsoft Dynamics Upgrade Blog Series Schedule to review upcoming blog posts related to upgrading Microsoft Dynamics GP and other helpful resources.