Knowing how to perform a Dynamics GP upgrade is only part of what you will need to do a successful upgrade. You may encounter some errors or issues during the upgrade process and knowing what to do to troubleshoot them is important.
Microsoft Dynamics GP Utilities reads the DU000010 table in the DYNAMICS/system database to get a list of tables that need to be updated between the original and new versions. Sometimes one table can go through more than one update during a database upgrade.
For example, you can run this script to find the list of tables that are converted for the Dynamics GP 2018 release:
Select fileOSName from DYNAMICS..DU000010 where CodeName like 'GP18%'
The key to troubleshooting table upgrade errors is to first understand what happens to each table. You can then trace those steps in the Dexsql.log. If a table fails during the table conversion, you can use the steps to find where the failure happened in the Dexsql.log.
Table Conversion Steps for Each Table
Table is Renamed: Key Word ‘RENAME’
The first step that occurs for a table going through an upgrade is that it gets renamed. This is done so that the new Microsoft dynamics GP table can be created. This is also important because it is like creating a backup of the table. An example of the naming convention that Dynamics GP Utilities uses to rename tables is the following:
GL00100 >> G00100L
SOP10100 >> S10100OP
As you can see with this example, the tables that start with 2 letters have the second letter placed at the end of the table. Those tables that begin with 3 letters have the second and third letter placed at the end.
**TIP: When looking at a Dexsql.log to troubleshoot a failed table upgrade error, always search on the renamed table. For example, if the GL00100 failed, you would start your search with G00100L to get to the start of the conversion process for that table.
The Beauty of the Upgrade – The db_status & Rollback! – You Don’t Need to Restore!
An important part of the upgrade process is that each step is tracked by the DB_Status column in the DB_Upgrade system table. This allows Microsoft Dynamics GP Utilities to ‘remember’ where it stated and left off for an upgrade. The DB_Status of 23 is the table conversion step. Since Utilities remembers where it left off, you do not need to restore the database(s) if the upgrade fails. If the upgrade does fail, you can close out of Utilities, fix the issue and then re-launch Utilities again. Because of the number in the DB_Status column in the DB_Upgrade system table, Utilities knows where it left off and will pick up from that step.
Microsoft Dynamics GP Utilities does a great job when individual tables fail. If a table fails to convert, Utilities will roll-back the table by deleting the new table and primary key, and then re-naming the old table and primary key back to their original name. For example, if the GL00100 table failed the upgrade conversion for any reason, Utilities would delete the new GL00100 table and primary key, and then rename the old G00100L table and primary key back to GL00100. This way we don’t have to worry about losing any records in the upgraded tables and it allows you to close out of Utilities, fix any issues causing upgrade failures, and then launch Utilities again without needing to restore.
Troubleshoot a Table Conversion Failure
When the table conversion fails, the following window will appear in Microsoft Dynamics GP Utilities. Microsoft Dynamics GP Utilities will stop on the first company that fails. For example, if you mark to upgrade 15 companies and the 5th company fails, Utilities will not continue to upgrade the remaining 10 companies.
Steps to Troubleshoot a Table Conversion Failure
Don’t Panic!!
The first step is to not panic when you see a Red X next to your company. Hopefully, you are running the test upgrade first so you have time to troubleshoot. If you are not running a test upgrade, this blog will help you get the issue resolves fast!
DO NOT Restore the company database
As mentioned above, Microsoft Dynamics GP Utilities remembers where it left off. All troubleshooting can take place at the point of failure and no restore is required.
Close out of Utilities
When the Red X appears next to the company, close out of Microsoft Dynamics GP Utilities to start the troubleshooting process.
Determine what tables failed
Run the following Failed Tables script found at the end of this article in SQL Server Management Studio to determine what tables failed. Pay particular attention to the errordes column. This column can provide enough detailed information to start troubleshooting without even looking at the Dexsql.log.
Based on the results above, check the Known Upgrade Issues and Critical Upgrade Issues listsEven if you checked the Known Issues List and Critical Upgrade Issues prior to the upgrade, it is a good idea to check them if tables failed during the table conversion.
Upgrading to Microsoft Dynamics GP | Microsoft Docs
If the issue is not a Known Issue or Critical issue, start troubleshooting!
Remember, that each table is rolled back if it fails, therefore, right now the SOP10200, IV70500 and the GL00201 are set back to the Microsoft Dynamics GP 2010 version, for this example. All troubleshooting can take place right in the failed state. DO NOT restore the database!
If you are opening a support case for a Dynamics GP database upgrade issue we would like this done first:
Run the following script: Delete DYNAMICS..DU000030 where Status <> 0 and Status <> 15
Capture a DEXSQL.LOG of the failing process in GP Utilities:
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;850996
Launch Dynamics GP Utilities and continue the upgrade of the system or company database that is failing the upgrade, which should fail on the same errors.
Once the upgrade fails again on this or any other errors, please send the dexsql.log file that was created, any error information from Utilities and the results of the following scripts:
Select CMPANYID,CMPNYNAM,INTERID from DYNAMICS..SY01500
Select * from DYNAMICS..DB_Upgrade
Select * from DYNAMICS..DU000020 order by companyID
/*Failed Tables Script*/
SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber
AND a.PRODID = b.PRODID
JOIN
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15
Please continue to check out our Microsoft Dynamics GP 2025 Upgrade Blog Series Schedule for the remaining blogs!
