SQL Script to Notify When Upgrade Complete

Microsoft Dynamics GPThis SQL script was written for a client with a large number of databases and which took a long time to update and they needed to upgrade through several versions of Microsoft Dynamics GP. The script checks for start and end times in the DB_UPGRADE table; when they match for all rows, an error is raised.

The script was scheduled using SQL Server Agent which was configured to send an email when the error was raised; this allowed him to know when part of the upgrade had completed and the next stage needed to be started.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @UpgradeComplete AS INT

SELECT
	@UpgradeComplete = CASE WHEN COUNT(PRODID) = 0 THEN 1 ELSE 0 END
FROM
	DB_UPGRADE
WHERE
	start_time = stop_time

IF (@UpgradeComplete = 1)
	/*
	throw error because all companies upgraded
	if <> 0, that means upgrade still running and nothing needs to occur
	*/
	RAISERROR ('GP 2016 upgrade complete; next stage needs to be started',11,1)

Workflow Approval Request Error After Upgrade

Microsoft Dynamics GPI’ve assisted a few clients to upgrade to Microsoft Dynamics GP 2018 recently and have seen two different errors cause problems with Workflow.

As part of the upgrade process, database objects are deployed and various scripts run against them. This includes the wfdeployclrassemblies stored procedure which deploys the .NET Assemblies required by Workflow for such tasks as AD lookups and sending of emails.

However, I’m going to make the running of this stored procedure a standard, manual, part of the upgrade as I am finding that if it isn’t run after the upgrade is complete, that AD lookups don’t work until it is (this usually manifests in approvals failing to find a user to email or approvals failing if the user had the email from before the upgrade). I might was preempt and do it manually every time in order to give clients a smoother process. To run the stored proc, open SQL Server Management Studio and, against the system database, typically called DYNAMICS run the following (it’ll take a few minutes):

exec wfdeployclrassemblies

The other issue I’ve now seen more than once (and have also seen when upgrading some clients to prior versions), is that during the upgrade the Workflow managers are lost:

Workflow Maintenance

Continue reading “Workflow Approval Request Error After Upgrade”

Microsoft Dynamics GP 2013 Nearing End of Mainstream Support

Microsoft Dynamics GPMicrosoft Dynamics GP 2013 was released back on 12th March 2013; mainstream support is available for versions of Dynamics GP for approximately five years from the release date. Therefore mainstream support for Dynamics GP 2013 ends on 10th April 2018, after which it will go into extended support.

My recommendation to clients is to try to remain on a mainstream supported version of the product so that you can still receive hot fixes to major issues (or for US or Canadian users receive payroll updates).

With the frequency of releases of Dynamics GP, an upgrade every five years is going to mean an upgrade between five versions which is quite a jump (that’s going forward; for a while recently new versions were twice a year); I generally advise clients to upgrade every 2-3 versions to minimise the length of time an upgrade will take and how many intermediate versions need to be upgraded through (we have one client who does the ideal and upgrades once per year, but they largely do their own upgrades with only a small amount of input from myself).

Upgrading often means you have most of the bug fixes and regularly get access to the new functionality.

You can see the launch dates as well as the mainstream and extended support dates on the Microsoft Product Lifecycle page.

My recommendation is, if you are on Microsoft Dynamics GP 2013, and haven’t already started planning your upgrade, to start now.

Analytical Accounting Microsoft Dynamics GP 2018 Upgrade Error

Microsoft Dynamics GPOver the last couple of weeks we have been on a bit of an upgrade splurge with clients; a number of them are doing test upgrades to Microsoft Dynamics GP 2018 from a variety of versions. One client is upgrading from Microsoft Dynamics GP 2016 RTM to 2018 RTM; according to the Upgrading Microsoft Dynamics GP hot topic, Microsoft Dynamics GP 2016 can be upgraded from version 16.00.0439 or later; however, when Analytical Accounting is installed, this is not correct.

To upgrade the client, we upgraded them to the last hotfix for Microsoft Dynamics GP 2016 (KB4056559 which is version 16.00.0641) and then to 2018; however, GP Utilities for 2018 errored saying that it could not upgrade Analytical Accounting from that version. Fortunately, we did not have to go back to a backup and redo the upgrade, as, while AA in installed, this particular client does not use the module.

I used the scripts to remove the tables and other database objects and was then able to run the 2018 GP Utilities to do the database upgrade without further issue.

I have previously upgraded a test system from 2016 to 2018 without issue; this was from KB3194397 (version 16.00.0558). We do have a small number of other clients with AA installed, so we now know what version of GP 2016 to upgrade them from to avoid this issue.

Scripts to Remove Analytical Accounting

Microsoft Dynamics GPBack in February 2017, I posted about a Microsoft Knowledge Base article on removing Analytical Accounting (AA) from Microsoft Dynamics GP. At the time, the KB article was unavailable, but the download links still worked (if you knew what they are).

I was contacted by someone recently asking if I had a copy of the scripts as the download links no longer work (the KB article itself is available).

This was unusual timing as I had just been onsite with a client where we encountered issues upgrading their system from 2016 RTM to 2018 RTM. In that case, the solution was to remove AA from the system as they had the module used, but never implemented it.

To remove AA, I dipped into my script library for the AA removal scripts from KB915903. After being alerted at the scripts were no longer available, I thought it might be useful to others to post them here.

The scripts are available from the following links:

  1. KB915903_AA_Remove_AACompete_DYNAMICS_10 – this script needs to be run against the system database (typically called DYNAMICS.
  2. KB915903_10 – this script should be run against all of the company databases.
  3. KB915903_DYNAMICS_Remove_DB_Upgrade_10 – this script is run against the system database.

Removing Analytical Accounting from Microsoft Dynamics GP

Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

  1. Company Database Script
  2. System Database Script

After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:

DECLARE @PRODID INT = 3180

DELETE DB_Upgrade WHERE PRODID = @PRODID
DELETE DU000020 WHERE PRODID = @PRODID

As always before running scripts, make sure you have a good backup of your databases.

UPDATED 20/01/2018: Download links updated to working ones

SQL Script To Log Into Each Company

Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn’t a problem as Microsoft Dynamics GP supports this very well.

However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

Doesn’t sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

I removed the temporary table from Aaron’s script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	'# DEXVERSION=16.00.0033.000 2 2
	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
		  MoveTo field ''OK Button'' 
		  ClickHit field ''OK Button''
		NewActiveWin dictionary ''default''  form sheLL window sheLL 
		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
		CASE WHEN RTRIM(CMPNYNAM) LIKE '%<TEST>' THEN
			'
			  # This company is set up for testing only. Do not use this company when processing live data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN RTRIM(CMPNYNAM) LIKE '%<HISTORICAL>' THEN
			'
			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN CMPANYID = -1 THEN
			'
			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
			  ClickHit field OK 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		ELSE
			''
		END
FROM
	DYNAMICS..SY01500
ORDER BY
	CMPNYNAM