Microsoft Dynamics GP Upgrade 2024 - Known Upgrade Issues

Lucas Miller

Like(0)

Report

Hello Dynamics GP Community!

In this article we will be covering the known issues that we've had reported with Microsoft Dynamics GP as well as the resolutions available for these issues.

With any upgrade, we recommend reviewing all documentation and known issues prior to the upgrade process 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 upgrade issues we've encountered in the past for Microsoft Dynamics GP. If anything new is reported we will be sure to get them added here as well.

1/16/2025 UPDATE:

After digging into additional occurrences of this issue it has been determined that there may be issues with document attachments on General Ledger transactions, even if you don't encounter an error message during the upgrade. The upgrade process may use an incorrect Transaction Date when updating the BusObjKey value in the CO00102, CO00103, CO00104 and CO00105 tables. The issue will be presented as missing attachments on Journal Entries after the upgrade.

The best solution we have found is to take the pre-upgrade BusObjKey values for GL Transactions and update them in-place rather than use the formula that Dynamics GP Utilities is using. You can either catch this before the upgrade, or use a pre-upgrade version of the company database with the following steps. As always, make sure you have a good backup of all databases involved before performing a task like this.

Also, this fix has been written for customer environments where the regional setting would have been MM/DD/YYYY for the date format. When you look at the BusObjKey for your Journal Entries the last portion of the string will be the date format:

SELECT BusObjKey FROM CO00102 where BusObjKey Like '%0\GL\Transaction Entry\%/%'

The upgrade is changing that date format to YYYYMMDD for all GP customers, regardless of their regional date settings, in order to standardize the data.

If you note a different date format then you may need a custom version of the CO_Tables_MM_DD_YYYY.sql script. Please contact Microsoft Dynamics GP support for assistance with this.

NOTE: These steps are meant for a situation where you have completed the upgrade to Dynamics GP 18.7, discovered that GL transactions are missing attachments and are looking to recover those.

Restore a pre-upgrade backup of your company database to a new database, which I will refer to as PREUPGRADE
Within the PREUPGRADE database run the following queries to create CO*_GLATTACH backups of the 4 CO* tables that contain just the GL Transaction attachments

SELECT * INTO CO00102_GLATTACH from CO00102 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00103_GLATTACH from CO00103 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00104_GLATTACH from CO00104 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00105_GLATTACH from CO00105 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'

Then run the CO_Tables_MM_DD_YYYY.sql script against the PREUPGRADE database and it will update the BusObjKey values in these CO*_GLATTACH tables with the proper date formats for GP 18.7.
Once that is done you’ll need to move those updated records from the _GLATTACH tables in PREUPGRADE to your POSTUPGRADE company database

a. First you’ll want to backup the GL Transaction attachment records in these tables by running these against POSTUPGRADE:

b. You’ll then want to delete the GL Transaction records from the POSTUPGRADE tables

i. What could be tricky here is if you’ve been adding new attachments since the upgrade to GP 18.7. You may need to look at the CREATDDT for the records in the CO* tables and maybe use that as another filter in the WHERE clause to only remove attachment records that would have existed before the upgrade date, leaving all of the new records untouched since they should all be working.

c. Then you’ll need to insert the records from PREUPGRADE to POSTUPGRADE:

INSERT INTO POSTUPGRADE..CO00102 (AllowAttachmentEmail,AllowAttachmentFlow,Attachment_ID,AttachmentOrigin,BusObjKey,CREATDDT,
CREATETIME,CRUSRID,DELETE1,HISTRX,WorkflowStepInstanceID)
SELECT AllowAttachmentEmail,AllowAttachmentFlow,Attachment_ID,AttachmentOrigin,BusObjKey,CREATDDT,
CREATETIME,CRUSRID,DELETE1,HISTRX,WorkflowStepInstanceID
FROM PREUPGRADE..CO00102_GLATTACH

INSERT INTO POSTUPGRADE..CO00103 (AllowAttachmentsEmail,AttachAttributeDefault,Attachment_ID,AttributeShortName_1,AttributeShortName_2,AttributeShortName_3,
AttributeShortName_4,AttributeShortName_5,BusObjKey,DefaultAttachType)
SELECT AllowAttachmentsEmail,AttachAttributeDefault,Attachment_ID,AttributeShortName_1,AttributeShortName_2,AttributeShortName_3,
AttributeShortName_4,AttributeShortName_5,BusObjKey,DefaultAttachType
FROM PREUPGRADE..CO00103_GLATTACH

INSERT INTO POSTUPGRADE..CO00104 (Attachment_ID,AttachmentStatus,BusObjKey,CREATDDT,CREATETIME,CRUSRID)
SELECT Attachment_ID,AttachmentStatus,BusObjKey,CREATDDT,CREATETIME,CRUSRID
FROM PREUPGRADE..CO00104_GLATTACH

INSERT INTO POSTUPGRADE..CO00105 (Attachment_ID,BusObjKey,CREATDDT,CREATETIME,DELETE1,DOCNUMBR,
EmailAllowAttachments,fileName,FileType,ORD,Size,STRTDSCR)
SELECT Attachment_ID,BusObjKey,CREATDDT,CREATETIME,DELETE1,DOCNUMBR,
EmailAllowAttachments,fileName,FileType,ORD,Size,STRTDSCR
FROM PREUPGRADE..CO00105_GLATTACH

NOTE: Replace the database names in these scripts as appropriate.

After this has been run you can start spot-checking the Journal Entries that have attachments in the table to make sure they show up correctly in the GP client.

If you're approaching this before the upgrade then you don't need the PREUPGRADE database and the steps will be:

Run the following queries against the company database to create CO*_GLATTACH backups of the 4 CO* tables that contain just the GL Transaction attachments
SELECT * INTO CO00102_GLATTACH from CO00102 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00103_GLATTACH from CO00103 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00104_GLATTACH from CO00104 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
SELECT * INTO CO00105_GLATTACH from CO00105 (nolock) where BusObjKey Like '%0\GL\Transaction Entry\%/%'
Then run the CO_Tables_MM_DD_YYYY.sql script against the company database and it will update the BusObjKey values in these CO*_GLATTACH tables with the proper date formats for GP 18.7.
Once that is done you’ll need to remove the GL Transaction information from the CO* tables in this company database:

DELETE CO00102 where BusObjKey Like '%0\GL\Transaction Entry\%'
DELETE CO00103 where BusObjKey Like '%0\GL\Transaction Entry\%'
DELETE CO00104 where BusObjKey Like '%0\GL\Transaction Entry\%'
DELETE CO00105 where BusObjKey Like ‘%0\GL\Transaction Entry\%’

4. You can then proceed with your upgrade to GP 18.7 and it should complete without any errors regarding these tables.

5. Then you’ll need to insert the records from CO*_GLATTACH tables back into their corresponding CO* tables:

INSERT INTO CO00102 (AllowAttachmentEmail,AllowAttachmentFlow,Attachment_ID,AttachmentOrigin,BusObjKey,CREATDDT,
CREATETIME,CRUSRID,DELETE1,HISTRX,WorkflowStepInstanceID)
SELECT AllowAttachmentEmail,AllowAttachmentFlow,Attachment_ID,AttachmentOrigin,BusObjKey,CREATDDT,
CREATETIME,CRUSRID,DELETE1,HISTRX,WorkflowStepInstanceID
FROM CO00102_GLATTACH

INSERT INTO CO00103 (AllowAttachmentsEmail,AttachAttributeDefault,Attachment_ID,AttributeShortName_1,AttributeShortName_2,AttributeShortName_3,
AttributeShortName_4,AttributeShortName_5,BusObjKey,DefaultAttachType)
SELECT AllowAttachmentsEmail,AttachAttributeDefault,Attachment_ID,AttributeShortName_1,AttributeShortName_2,AttributeShortName_3,
AttributeShortName_4,AttributeShortName_5,BusObjKey,DefaultAttachType
FROM CO00103_GLATTACH

INSERT INTO CO00104 (Attachment_ID,AttachmentStatus,BusObjKey,CREATDDT,CREATETIME,CRUSRID)
SELECT Attachment_ID,AttachmentStatus,BusObjKey,CREATDDT,CREATETIME,CRUSRID
FROM CO00104_GLATTACH

INSERT INTO CO00105 (Attachment_ID,BusObjKey,CREATDDT,CREATETIME,DELETE1,DOCNUMBR,
EmailAllowAttachments,fileName,FileType,ORD,Size,STRTDSCR)
SELECT Attachment_ID,BusObjKey,CREATDDT,CREATETIME,DELETE1,DOCNUMBR,
EmailAllowAttachments,fileName,FileType,ORD,Size,STRTDSCR
FROM CO00105_GLATTACH

12/11/2024 UPDATE: Document Attachment Reference (CO00102) conversion error

We have recently had a few reports of the CO00102 table failing to convert during the upgrade to Dynamics GP 18.7 and/or the 2024 Year End patch. It appears that a product issue in a previous version of Dynamics GP that was addressed a while ago allowed some invalid data to be inserted into this table. As part of a change to accommodate more international date formats the BusObjKey value in this table is being updated. If a General Ledger transaction has more than one document attachment and they have the same Attachment_ID (which should not be possible under normal circumstances) you may run into an error like this during the upgrade:

To check if you may run into this condition you can run the following query against each company database prior to the upgrade to Dynamics GP 18.7:

SELECT Attachment_ID, CREATDDT FROM CO00102
WHERE BusObjKey like '%GL\Transaction Entry%'
GROUP BY Attachment_ID, CREATDDT
HAVING COUNT(*) > 1

Review the records that are returned. If any two or more records with the same Attachment_ID value also have the same CREATDDT value then you will likely run into this error. If nothing is returned you should be free to proceed with your upgrade as usual.

The current workaround for this issue involves identifying the duplicate records in the CO00102 table, correcting the CREATEDDT value so they are not the same between them and then allowing the upgrade to perform it's process. The key here is that the CREATEDDT value in this table will need to match the TRXDATE value for the Journal Entry as held in the GL10000, GL20000 or GL30000 table in order to maintain the link to the document attachment.

If you are running into this issue, or if you have done something like moving records out of the CO00102 table to get through the upgrade we would like you to open a new support case so we can assist you with correcting the damaged data in a way that will ensure that your document attachments are still intact after the upgrade. This will be considered a non-chargeable support incident.

When you open the case please mention that you are experiencing the known CO00102 upgrade issue. We would also need to see the results of the following query to begin putting together scripts for correcting the issue:

SELECT Attachment_ID, CREATDDT FROM CO00102
WHERE BusObjKey like '%GL\Transaction Entry%'
GROUP BY Attachment_ID, CREATDDT
HAVING COUNT(*) > 1

We will be reviewing this issue for a fix in an upcoming release of Dynamics GP.

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:

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

In SQL Server Management Studio, right-click your database and click properties.
Select Options on the left side list.
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.

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:

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:

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

Run the following scripts to drop the original tables.

DROP TABLE EHW20200
DROP TABLE EHW30200
DROP TABLE EHW40200

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.

Run these scripts against any company failing the upgrade to restore the data back from backups created in step 1.

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.

Community site session details