Microsoft Dynamics GP 2010 Utilities “Hanging” when Upgrading the Databases with Service Pack 3 or any Later Hotfix

  • Article

David Meego - Click for blog homepageLast month, Kelly Youells posted on the Dynamics GP Support and Services Blog about an issue which can cause Microsoft Dynamics GP 2010 Utilities to hang when updating to Service Pack 3 or later. The details of the issue are posted on the blog and also linked to the Dynamics GP 2010 Hot Topic:

 

The quick summary is that Dynamics GP does not allow fiscal/financial periods to overlap any more. If there are overlapping periods, the upgrade process will go into an infinite loop and appear to hang.

Kelly provides the following query to look for 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 a.YEAR1, a.PERIODID

For example: The query highlighted the 13th period of the 2013 year was overlapping for the 30th June 2013.

2013 12 2013-06-01 00:00:00.000 2013-06-30 00:00:00.000
2013 13 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000

She also provides example update statements to adjust the periods, for our example:

update SY40100
set PERDENDT = '2013-06-29 00:00:00'
where YEAR1 = 2013 and PERIODID = 12

update SY40100
set PERIODDT = '2013-06-30 00:00:00', PERDENDT = '2013-06-30 00:00:00'
where YEAR1 = 2013 and PERIODID = 13

This additional end of year period is quite common and the queries provided in the article will help resolve overlapping periods caused by this situation.

 

However, I had a recent case where we still had a number of overlapping periods reported by the original query and not enough information to understand why they were being reported. Updating the original query to add the columns from the other half of the query helped us identify what was wrong.

SELECT distinct a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT
              , b.YEAR1, b.PERIODID, b.PERIODDT, b.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 a.YEAR1, a.PERIODID

We identified two situations which were causing overlapping periods to be reported:

  1. The period starting and/or ending dates for the various series and document sources did not match the master (series = 0) dates.
  2. The period starting and/or ending dates for the various series and document sources were not set... left as 1900-01-01.

The following query detects this incorrect data:

select a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT
, b.PERIODDT, b.PERDENDT, b.SERIES, b.ODESCTN
FROM SY40100 a JOIN SY40100 b
ON a.YEAR1 = b.YEAR1 AND a.PERIODID = b.PERIODID AND a.SERIES = 0
where a.PERIODDT <> b.PERIODDT or a.PERDENDT <> b.PERDENDT
or b.PERIODDT = '1900-01-01 00:00:00' or b.PERDENDT = '1900-01-01 00:00:00'
order by a.YEAR1, a.PERIODID, b.SERIES, b.ODESCTN

If the above query returns results, you can fix the data using the following update statement: 

update b set b.PERIODDT = a.PERIODDT, b.PERDENDT = a.PERDENDT
FROM SY40100 a JOIN SY40100 b
ON a.YEAR1 = b.YEAR1 AND a.PERIODID = b.PERIODID AND a.SERIES = 0
where a.PERIODDT <> b.PERIODDT or a.PERDENDT <> b.PERDENDT
or b.PERIODDT = '1900-01-01 00:00:00' or b.PERDENDT = '1900-01-01 00:00:00'

After running the above update statement we still had overlapping periods reported. Further research identified data in the period table when there was no matching year set up. The following query will detect the orphaned data.

select a.YEAR1, a.PERIODID, a.PERIODDT, a.PERDENDT, a.SERIES, a.ODESCTN
, b.YEAR1
FROM SY40100 a LEFT OUTER JOIN SY40101 b
ON a.YEAR1 = b.YEAR1
where b.YEAR1 IS NULL

If the above query returns results, you can remove the orphaned records using the following delete statement:

delete a
FROM SY40100 a LEFT OUTER JOIN SY40101 b
ON a.YEAR1 = b.YEAR1
where b.YEAR1 IS NULL

After we fixed the incorrect or missing dates and removed the orphaned records, the original query provided by Kelly no longer reported any overlapping periods. We repeated this process for each of the companies.

We were then able to proceed with the upgrades of all of the companies without further hanging issues.

Please don't forget the final step: After the upgrade has completed, you must reconcile the General Ledger Years to ensure that transactions show correctly in the now modified fiscal/financial periods.

Hope this additional information was helpful.

David