Happy Thursday!  We have seen a few cases come in where Microsoft Dynamics GP 2010 Utilities is “hanging” when upgrading the databases with Service Pack 3 or any later hotfix or upgrading from a previous release to Microsoft Dynamics GP 2010 Service Pack 3 or later.  If you are upgrading to Service Pack 3 or later and Utilities is processing for a long time, check your Dexsql.log if you enabled it.  If the Dexsql.log is no longer growing in size, open the Dexsql.log to view the last entry.  If the last entry in the Dexsql.log is the following, you are running into the new issue we have seen with Service Pack 3 or later:

/*  Date: 07/24/2012  Time: 15:42:57
stmt(51310568):*/
if OBJECT_ID(N'tempdb.[dbo].[#dates]') IS NOT NULL  drop table #dates  select  YEAR1, PERIODID, PERIODDT, PERDENDT into #dates  from SY40100 where SERIES = 2 and PERDENDT <> ''  group by YEAR1, PERIODID, PERIODDT, PERDENDT   CREATE CLUSTERED INDEX dates_CL ON #dates  (YEAR1 ASC, PERIODID ASC, PERIODDT ASC, PERDENDT ASC)  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  update top (500000) a set a.PERIODID = b.PERIODID  from GL30000 a,#dates b  where a.HSTYEAR = b.YEAR1 and  a.TRXDATE >= b.PERIODDT and a.TRXDATE <= b.PERDENDT  and a.PERIODID <> b.PERIODID  while @@ROWCOUNT > 0  BEGIN  update top (500000) a set a.PERIODID = b.PERIODID  from GL30000 a,#dates b  where a.HSTYEAR = b.YEAR1 and  a.TRXDATE >= b.PERIODDT and a.TRXDATE <= b.PERDENDT  and a.PERIODID <> b.PERIODID  end


If you do not have a Dexsql.log running, you can also start a SQL Server Profile trace to verify if you see the following statement continually repeated:

update top (500000) a set a.PERIODID = b.PERIODID from GL30000 a,#dates b where a.HSTYEAR = b.YEAR1 and a.TRXDATE >= b.PERIODDT and a.TRXDATE <= b.PERDENDT and a.PERIODID <> b.PERIODID while @@ROWCOUNT > 0 BEGIN update top (500000) a set a.PERIODID = b.PERIODID from GL30000 a,#dates b where a.HSTYEAR = b.YEAR1 and a.TRXDATE >= b.PERIODDT and a.TRXDATE <= b.PERDENDT and a.PERIODID <> b.PERIODID


Background Information:

Microsoft Dynamics GP 2010 Service Pack 1 introduced a change to the GL20000 and GL30000 to accommodate the use of Multicurrency with Management Reporter.  This change required an update statement to the GL20000 and the GL30000 during Microsoft Dynamics GP 2010 Utilities.  Several Customers ran into performance issues where the database upgrade was taking several hours due to the millions and millions of records in the GL30000 table.  The update statement was causing a table scan on the GL20000 and GL30000 thus causing Utilities to take a very long time.  Because of that issue, we made a change to improve performance in Service Pack 3 for Microsoft Dynamics GP 2010.  This change is also included in all later hotfixes following Service Pack 3.

 

The NEW Issue:

The new change in Service Pack 3 or later now relies on the PERIODDT and PERDENDT columns in the SY40100 (Period Setup) table.  The PERIODDT is the start date for the fiscal period and the PERDENDT is the end date for the fiscal period.  If these dates overlap between periods for a particular fiscal or calendar year, Microsoft Dynamics GP 2010 Utilities will get in a loop and appear to “hang”.

 

The Resolution:

There is a pretty quick fix for this issue, check out the resolutions below:


Resolution 1: Pre-Upgrade – You have not launched Microsoft Dynamics GP 2010 Utilities to upgrade with Service Pack 3 or any later hotfix

1. Open the SQL Server Management Studio and run the script below to verify if any fiscal period dates overlap:


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

2. If results are returned, the quick fix is to login to Microsoft Dynamics GP and go to Microsoft Dynamics GP | Tools | Setup | Company | Fiscal Periods and fix the dates in the Fiscal Periods Setup window.

3. Almost all of the cases we have seen are with years that have over 12 periods.  For example, if the year is a calendar year and there are 13 periods,  Period 12 and Period 13 should look like the following according to the following article:
http://support.microsoft.com/kb/871679


In the SY40100 table…

Period 12
PERIODDT = 12/1/XXXX
PERDENDT = 12/30/XXXX

Period 13
PERIODDT = 12/31/XXXX
PERDENDT = 12/31/XXXX


In the Fiscal Periods Setup Window…

image

 

****The key is that the dates do not overlap between periods.****

4. Once the dates are fixed in the Fiscal Periods Setup window, run the script from Step 1 above again to verify no results are returned.

5. Make another backup of the company databases now that a change was made.

6. Launch Microsoft Dynamics GP 2010 Utilities to upgrade the databases.

7. Once the upgrade is complete, login to Microsoft Dynamics GP 2010 and go to Microsoft Dynamics GP | Tools | Utilities | Reconcile and reconcile the GL years.

 

Resolution 2: During the Upgrade – You have launched Microsoft Dynamics GP 2010 Utilities to upgrade with Service Pack 3 or any later hotfix and Utilities is “hanging”

1. Close out of Microsoft Dynamics GP 2010 Utilities since it is “hanging”.

2. Open the SQL Server Management Studio and run the script below to verify if any fiscal period dates overlap:


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

3. If results are returned and this is a test upgrade and you are not too far into the process, it may be easier to restore the databases and use Resolution 1 above.

4. If this is a live upgrade and several databases have already upgraded successfully, the issue can be fixed in the SQL Server Management Studio.

5. Do not restore the company database.  All troubleshooting can take place at the point of the “hang”.

6. Run the script below to clear the dulck.

delete DYNAMICS..duLCK

7. Make sure you have a good backup of each company database that has results returned from the script above in Step 2.

8. Based on the results returned in Step 2 above, you need to determine what the PERIODDT and PERDENDT should actually be for the fiscal/calendar year.  Again, the key is that the dates do not overlap. **Important**Please discuss with your Team what the fiscal periods should look like for your organization.  For example, let’s assume the following results were returned:

YEAR1 PERIODID PERIODDT PERDENDT
2010 12 2010-12-01 00:00:00.000 2010-12-31 00:00:00.000
2010 13 2010-12-01 00:00:00.000 2010-12-31 00:00:00.000
2010 14 2010-12-31 00:00:00.000 2010-12-31 00:00:00.000


The above results indicate that the 2010 year has 14 periods and is a calendar year.  The 2010 year was not setup correctly as defined in the following article:
http://support.microsoft.com/kb/871679


To fix the overlapping dates for year 2010, the following update statements can be run:

update SY40100
set PERDENDT = '2010-12-29 00:00:00.000'
where YEAR1 = 2010 and PERIODID = 12

update SY40100
set PERIODDT = '2010-12-30 00:00:00.000',
PERDENDT = '2010-12-30 00:00:00.000'
where YEAR1 = 2010 and PERIODID = 13

update SY40100
set PERIODDT = '2010-12-31 00:00:00.000',
PERDENDT = '2010-12-31 00:00:00.000'
where YEAR1 = 2010 and PERIODID = 14


9. Run the script from Step 2 to verify no results are returned.

10. Repeat steps 2-9 for each company database.

11. If changes were made to the database, make another backup of the company database.

12. Start Microsoft Dynamics GP 2010 Utilities again and Utilities should continue past the “hang”.

13. Once the upgrade is complete, login to Microsoft Dynamics GP 2010 and go to Microsoft Dynamics GP | Tools | Utilities | Reconcile and reconcile the GL years.

If you have any questions, please work with your Partner for assistance or contact Microsoft Technical Support.

Happy Upgrading!!!