Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
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:You may encounter an issue with the ASIEXP86 table (SmartList Favorites Columns Master) when upgrading to Microsoft Dynamics GP 2018 R2: "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 and have ran into an error on the ASIEXP86 table in the System Database, you can perform the following steps:
1. Start a Dexsql.log file for Dynamics GP 2018 R2 and delete any other logs that may previously exist:How to create a Dexsql.log file to troubleshoot error messages in Microsoft Dynamics GP https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;850996
2. Launch Microsoft Dynamics GP 2018 R2 Utilities and begin the upgrade of the DYNAMICS system database.3. You will again encounter the same error on the ASIEXP86 table.
At that time, look in the dexsql.log file for the duplicate key, which will look something like this:
exec duAN_SynchronizeTable 'ASIEXP86','ASIEXP86_T','D'
[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert duplicate key row in object 'dbo.ASIEXP86' with unique index 'AK2ASIEXP86'. The duplicate key value is (0, 5, 0, 0, , , */
Make note of the results.
4. If you get a duplicate index record error as referenced in Step 3 we need to remove all the records that begin with, in this example, 0, 5, 0, 0, which we can do by using a script such as this:DELETE DYNAMICS..ASIEXP86 WHERE ASI_Favorite_Dict_ID = 0AND ASI_Favorite_Type = 5AND ASI_Favorite_Save_Level = 0AND CMPANYID = 0
5. Do this for any further duplicate errors that you encounter on the ASIEXP86 until you get past the 'Load Required Data' which is where the ASIEXP86 fails.
We will look to get this fixed in a future service pack 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 6th, 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.sqlCO00105_Cleanup.sqlThe 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
Karl, The 2018R2 may potentially has brought back the issue with the inquiry by document number in payables. After a 2018R2 upgrade, we are seeing 5-10 minutes to open this window, and GP hangs - and has to be terminated with task manager. I'm going to open a support case.