12 Oct 2017 8:03 PM Now that you know how to upgrade to Microsoft Dynamics GP 2018, the next thing we’ll look at is how to troubleshoot any errors or issues that you may see during a database upgrade. The most common upgrade support cases for Microsoft Dynamics GP that we take on a daily basis include: 1. Version Checks and Upgrades 2. Modified Dictionary Upgrade 3. Known Upgrade Issues 4. Database Upgrade Chris shared a great blog on version checks and Sarah shared a great blog on how to upgrade your modified reports and forms. Those blogs will get you on the right track for those areas. Check them out again for review!! In this blog, I will focus on the table conversion. The table conversion is the step in the upgrade where a certain set of tables go through a conversion process for the required Microsoft Dynamics GP 2018 feature changes. Not each and every table gets updated each time a database goes through an upgrade to a new version/build of Microsoft Dynamics GP. Microsoft Dynamics GP Utilities uses the DU000010 table in the DYNAMICS/system database, which contains a list of table updates, and it uses the version/build that you’re starting from and then the version/build that you’re upgrading to for Microsoft Dynamics GP. Using that information, it gets 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. You can run the scripts below to find the list of tables that are converted for each Microsoft Dynamics GP release: Microsoft Dynamics GP 2018 >>Select fileOSName from DYNAMICS..DU000010 where CodeName = 'GP18' Microsoft Dynamics GP 2016 R2 >> Select fileOSName from DYNAMICS..DU000010 where CodeName = ‘GP16R2’ Microsoft Dynamics GP 2016 >>Select fileOSName from DYNAMICS..DU000010 where CodeName = 'GP16' Microsoft Dynamics GP 2015 R2 >> Select fileOSName from DYNAMICS..DU000010 where CodeName = ‘GP14SP2’ Microsoft Dynamics GP 2015 >> Select fileOSName from DYNAMICS..DU000010 where CodeName = 'GP14' Microsoft Dynamics GP 2013 R2 >> Select fileOSName from DYNAMICS..DU000010 where CodeName = 'GP12SP3' Microsoft Dynamics GP 2013 Select fileOSName from DYNAMICS..DU000010 where CodeName = 'GP12' You can also refer to the Microsoft Dynamics GP 2018 Software Development Kit (SDK) for detailed table changes from the prior release: --In the Dynamics GP 2018 DVD, go into the Tools > SDK > Dynamics GP directory and you'll find the SDK.exe and Microsoft_DynamicsGP18_SDK_x86_en-us.msi files to install it with. 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. The Dexsql.log is a tracing tool that will log everything that Microsoft Dynamics GP 2018 Utilities is doing during the upgrade process. This includes the table conversion process itself. 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 1. 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 >> G00100LSOP10100 >> 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. Sometimes it can be a mix of letters and numbers, depending on the table. **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. 2. Primary Key is Renamed: Key Word ‘RENAME’ The primary key for each renamed table is also renamed so that the new primary key for the new Microsoft Dynamics GP table can also be created. This doesn’t necessarily mean the primary key is different than the previous release. 3. New Microsoft Dynamics GP 2018 table is created: Key Word ‘CREATE’Microsoft Dynamics GP Utilities uses the Dynamics.dic to create the new table in the required structure for Microsoft Dynamics GP 2018. The primary key, secondary indexes, dexterity procedures and bindings are also created during this process. **NOTE: **The ‘CREATE TABLE’ process is a very long process. In the Dexsql.log, you will see the start of the process with the CREATE TABLE. The end of the table creation process will be several “Default bound to column” messages like the following: 4. Check structure of new table, old table and/or dependent tables if applicable: Key Word ‘SELECT’ During the table upgrade process, Microsoft Dynamics GP Utilities may run SELECT statements against the original/renamed table, the newly created table, and/or even completely different tables that the current table being upgraded depends on. The SELECT statements are to validate the table structure of one or more tables. **NOTE: The ‘select’ statement can also be after the Insert statement as well 5. Records Inserted from Old Table to New Table: Key Word ‘INSERT’ After the new Microsoft Dynamics GP 2018 table is created and verified as being correct. Microsoft Dynamics GP Utilities will then insert any records that were in the original/renamed table. This is an important point of failure during the table upgrade process because if the old/renamed table is not in the correct structure, the insert will fail. Also, if the newly created table for Microsoft Dynamics GP 2018 is not in the correct structure, the insert will also fail. 6. Renamed table is dropped: Key Word ‘DROP’ Once the INSERT statement finishes inserting all records into the new table without any issues, then the old/re-named table is dropped. At the point of a table upgrading successfully and all data being inserted without issues, Microsoft Dynamics GP Utilities will set the Status for the table to a 0 in the DU000030 system table indicating the table was upgraded successfully 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 back to GL00100 along with its primary key. 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. Pretty AWESOME, eh? Let’s Troubleshoot a Table Conversion Failure! When the table conversion fails, the following window will appear in Microsoft Dynamics GP 2018 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 1. 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 resolve fast! 2. DO NOT Restore the company databaseAs 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. 3. Close out of UtilitiesWhen the Red X appears next to the company, close out of Microsoft Dynamics GP Utilities to start the troubleshooting process. 4. Determine what tables failedRun the script below in the SQL Server Management Studio to determine what tables failed. Pay particular attention to the errordes column. This column usually provides enough detailed information to start troubleshooting without even looking at the Dexsql.log. SELECT b.fileOSName, a.fileNumber, a.PRODID, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERIDFROM DYNAMICS.dbo.DU000030 aJOINDYNAMICS.dbo.DU000010 bON a.fileNumber = b.fileNumberAND a.PRODID = b.PRODIDJOINDYNAMICS.dbo.SY01500 cON a.companyID = c.CMPANYIDWHERE (a.Status <> 0 or a.errornum <> 0) and a.Status <>15 In this example, the following tables are failing: 5. 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 2018' hot topic: https://mbs.microsoft.com/customersource/northamerica/GP/support/hot-topics/HOT_TOPIC_MDGP2018Upgrade 6. Based on the results above start troubleshooting! If the issue is not a Known Issue or Critical issue, start troubleshooting!The errordes column above can provide great direction to start troubleshooting. In this example, we can use the fileNumber values for the failed tables to determine via the DU000010 table at what point these tables get upgraded. For example, if we look in the DU000010 table for the SOP10200 table and fileNumber 497, as per this script: Select * from DU000010 where fileOSName = 'SOP10200' We see that fileNumber 497 and SOP10200 is the update that takes place at the '11.80.1' which means that it gets upgraded between Dynamic GP 2010 and Dynamics GP 2013. Since we're upgrading, in this example, from Dynamics GP 2010 to Dynamics GP 2015, we know that this upgrade is taking place during the upgrade we're currently doing, so we need to find out why this table is failing. Since our 'failed tables' script mentions this table 'did not have the correct structure prior to the conversion', we would want to look at this table and compare it to the Dynamics GP 2010 version structure, since the tables get rolled back once they fail. If we find the structure is incorrect, we would need to re-create the table again and the Dynamics GP 2010 version, for this example, with any data it has in it, then delete the record for this failed table in the DU000030 table and continue with the upgrade to Dynamics GP 2015 R2, after fixing the two failing tables as well. The DU000010 table can also be used to determine when a table gets upgraded, as to determine whether a table may be failing a current upgrade, due to not being correctly upgraded at a prior version. Again 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! Ideally, if you are opening a support case for a Dynamics GP database upgrade issue/errors, we would like this information sent to us when the support case is created, as it will help expedite a resolution: A. Run the following script: Delete DYNAMICS..DU000030 where Status <> 0 and Status <> 15 B. Start a dexsql.log file for the Dynamics GP version you're launching Utilities from, and delete any prior dexsql.log files that may already exist: KB article 850996 - How to create a Dexsql.log file for Microsoft Dynamics GP and Great Plains https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;850996 C. 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 created, any error information from Utilities and then the results of the following scripts, because in the majority of upgrade cases, if it's failing on a table(s), this is what we're going to ask for: 1. Select CMPANYID,CMPNYNAM,INTERID from Dynamics..SY01500 2. Select * from Dynamics..DB_Upgrade 3. Select * from Dynamics..DU000020 order by companyID 4. 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 2018 and Year-End Upgrade Blog Series Schedule for the remaining blogs!