"Error Accessing SQL Data" message after 2020 Year-End update for Microsoft Dynamics GP

We've become aware of an issue being seen Microsoft Dynamics GP after applying the 2020 year-end update patch, both 18.3.1200 for Microsoft Dynamics GP and more commonly in 16.00.0864 for Microsoft Dynamics GP 2016.

After the upgrade, within the Microsoft Dynamics GP application, when attempting to post payables batches, print payables checks and other processes, an error message will show stating: "Error accessing SQL data".

Looking further, in the dexsql.log, it then shows an error: "number of results columns doesn't match table definition", referencing a call to the dexterity procedure zDP_PM00200SS_1, for the PM00200 table.

We've primarily seen this error occur due to reports dictionary files/modified reports not having been upgraded successfully, but we've also seen where the GP directory/code also didn't get updated correctly either, so, based on this, we have the following troubleshooting steps to resolve this error message:

A. Correct the modified reports

     1. Have all users log out of a Microsoft Dynamics GP instance and in the Customization Maintenance window ('Microsoft Dynamics GP' > Tools > Customize > Customization Maintenance), highlight or select all modified reports and export them into a package file.

     2. Open the package file in a text editor such as NotePad and look for a 'DBA' field reference for the PM_Vendor_MSTR table, which is the PM00200. 

          The reference will look like this:

= Table           "PM_Vendor_MSTR"

{

            ~Fields

            {

                        - TableField   "DBA"

            }

When we find this, we then need to modify it to be this:

= Table           "PM_Vendor_MSTR"

{

            ~Fields

            {

                        - TableField   "VENDDBA"

            }

***NOTE: Do not do a 'Replace All' of every reference of 'DBA' to 'VENDDBA', as we only want to change those fields related to the PM_Vendor_MSTR/PM00200 table.***

     3. Then save changes to this package file and log out of Dynamics GP.

     4. Take the existing reports dictionary files and cut/paste them into a backup folder.

     5. Log back into Dynamics GP and go into the Customization Maintenance window, which should not show any modified reports. If it does, then we didn't do steps 1-4 correctly and will need to go through those again.

     6. In the Customization Maintenance window, import the package file from step #1 into the Microsoft Dynamics GP instance which will re-create the reports dictionary files where the Dynamics.set file is showing them to exist.

         ***NOTE: This is necessary, as overriding the existing reports dictionary files, specifically Reports.dic, when we see this issue, does not work, as the dictionary itself is damaged and will just break the modified reports again.***

     7. In Microsoft Dynamics GP, try to re-create the error to verify whether it is resolved now or the issue still shows.

B. Resource Descriptions

If the issue still shows, verify the following:

     1. Login to Microsoft Dynamics GP and browse to 'Microsoft Dynamics GP' > Tools > Resource Descriptions > Tables, then in the 'Table Descriptions' window, click the ellipse button (...) next to the Table field, then in the Table Names window, select the following:

--Series: Purchasing

--View By: by table Physical Name

     2. In the list of tables, find and double-click on the 'PM Vendor Master File' / PM00200 table, then back in the Table Descriptions window for PM00200, look at the end of the list of columns/fields and verify that you see a field named 'DBA' which has a physical name of 'VENDDBA'.

If the field does not exist or the physical name shows as 'DBA' and not 'VENDDBA', that is an indicator that the Microsoft Dynamics GP code/directory itself hasn't been updated successfully and is potentially causing this error to occur.  In this case, continue with the next steps.....

C. Re-create Microsoft Dynamics GP code directory

     1. Have all users log out of a Microsoft Dynamics GP instance.

     2. Rename the GP directory from something like this >> C:\Program Files (x86)\Microsoft Dynamics\GP2016\

         ...to be this >> C:\Program Files (x86)\Microsoft Dynamics\GP2016_OLD\

     3. On the same machine, go to Control Panel > Programs > Programs and Features, right-click on this Microsoft Dynamics GP application and click 'Change'.

     4. In the options window that opens, choose the 'Repair' option and go through this process making sure it is successful.

     5. The Repair process will completely re-create the GP directory you just re-named, including all folders and files, minus any forms/reports dictionary files or third-party products and customizations.

     6. Login to this 'new' Microsoft Dynamics GP directory and see if you can re-create the error message or not.

         If not, import in the package file that was 'fixed' above, which will create the forms/reports dictionary files for this 'new' Microsoft Dynamics GP directory, then attempt to re-create this error message again to see if it is now resolved.

As mentioned, we've mainly seen this with the Microsoft Dynamics GP 2016 (16.00.0864) year-end update, but it can also occur with the Microsoft Dynamics GP (18.3.1200) year-end update as well.

If you run into this error, go over these troubleshooting steps, which will hopefully resolve the issue for you or at least identify the issue.

Please let us know if you have any questions or see anything different as well. Thank you