Upgrade error on the GLReverseHistYear Stored Procedure when upgrading to Microsoft Dynamics GP 2013 R2

We have recently seen an increase in calls of a failed upgrade to Microsoft Dynamics GP 2013 R2 for the GLReverseHistYear stored procedure. The issue seems to be caused by having installed Analytical Accounting (AA) at a prior version of Microsoft Dynamics GP but not upgrading it along the way with the other products because AA was no longer needed or wanted, however the KB to remove AA objects was not used to remove AA so there are remnants of AA in the Dynamics and Company databases.

Now if you look in your DYNUTILS.set file in the GP code folder, you will more than likely not see AA listed as you more than likely don’t even have AA installed since you are not AA, so therefore you would not expect Microsoft Dynamics GP Utilities to attempt to update any of the AA objects. 

Since AA is not listed as installed, GP Utilities functions as it should and the AA tables are not being updated.  However, when this stored procedure is being created, the stored procedure is created off of the GL tables and AA tables, and checks them for the Ledger_ID column.

If you are capturing a DexSQL.log during this Upgrade process, you will see the following entry:

create procedure GLReverseHistYear  @YearToOpen INT,  @O_iErrorState INT = NULL OUTPUT  as BEGIN  declare @sql1 NVARCHAR(4000),  @sql2 NVARCHAR(4000),  @sql3 NVARCHAR(4000),  @ColumnName VARCHAR(100),  @l_cBBF     VARCHAR(255),   @l_cPANDL   VARCHAR(255),   @l_cINTERID CHAR(5),  ……

ERROR [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'Ledger_ID'.

If AA was previously installed but never was used, you can use the KB Article below to completely remove AA.  Once that is done, during the Upgrade, the stored procedure will no longer see the AA tables and therefore the upgrade will not fail at that point when creating the GLReverseHistYear Stored Procedure because it will not see that the AA tables are missing the ‘Ledger_ID’ column because the AA tables will not exist.

However, if you do use AA or have previously used it in the past and don’t want to lose the data in those tables, you would need to install AA so that it is listed in your DYNUTILS.set file so that the AA tables themselves are upgraded properly.  Then when the GLReverseHistYear stored procedure is created, the AA tables will have the correct table structure, meaning that the Ledger_ID column is added earlier in the upgrade and the stored procedure will see that column during the process of creating that stored procedure and will not cause the upgrade to fail.

I do want to note, that AA needs to be on a version/build of Microsoft Dynamics GP that has a supported upgrade path.  For example, if AA was never upgraded from GP 8 or GP 9 and you are upgrading to Microsoft Dynamics GP 2013 from Microsoft Dynamics GP 2010, there is not a valid upgrade path for AA and the only option would be to use the KB to remove AA and start over from the beginning for AA as if it had never been installed.

Two options to resolve this upgrade issue:

1. Option 1: Install Analytical Accounting if you need to retain your AA history or would like to use AA going forward.
   1. Installing Analytical Accounting will then prompt you to upgrade Analytical Accounting which will update your AA tables which will then allow the Stored Procedure to get created successfully.  This will only work however if Analytical Accounting is on a version/build that has a valid upgrade path to the version/build of Microsoft Dynamics GP that you are upgrading to.

2. Option 2: Remove Analytical Accounting if you do not need to retain your AA history and do not plan on using it in GP 2013 R2.
   1. 915903 - Steps to remove Analytical Accounting from a computer that has Microsoft Dynamics GP installed
   2. https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;915903

Thanks!
Lance