Installing both msi & msp package together for Dynamics GP “R2” versions

For those with network deployment to consider, around one hundred machines we do, then Dynamics GP has a neat feature to “Create Installation Package”, which does just that. By selecting the option from the installation media it will package up the local fully configured GP client into an msi installer package that can then be used to install the fully configured GP client for other machines in the environment.

Generating an msi installer makes the operations team happy as they can push the msi using network management tools such as System Center Configuration Manager or in this case PDQ Deploy to the client machines that require it. Together with a colleague from the infrastructure team we stumbled our way to a solution that we are sharing here for ourselves and others to reference.

GP Media installer main page highlighting Create Installation Package

When used with GP2015R2 (or orther R2 version), the installation package is generated an .msi and a subfolder named updates that contains an .msp file (patch installer file).

Simply installing the msi will only install GP2015 client, launching the GP client after install will result in a white launch splash screen and it will clearly say version 14.00.0524 on the login screen. This is clearly not the R2 version, the package generator has created the base GP2015 install and then generated a separate patch file to take it to R2, the patch is in the updates folder.

Now try installing the .msp file, the client is upgraded to GP2015R2. When GP is launched the newer blue splash screen will be shown and version 14.00.0725 will be shown on the login screen.

If the requirement exists to push an install of GP2015R2 direct, not via GP2015, say for new client installs, rather than upgrades, then perhaps the ops team could run both installers in a serial fashion. However better way is to run the following command on the output GP packages;

msiexec.exe /a GreatPlains.msi /p Updates\MicrosoftDynamicsGP14-KB3063038-ENU.msp

This will suck the .msp patch file into the main installer file, making for a much quicker install and makes the deployment so much simpler for the software management infrastructure to push. Now only the newly merged msi needs to be deployed.

Dynamics GP 2015R2 & Dynamics GP 2016 Visual Studio Tools SDK download

Like many I’m used to just downloading my developer resources from MSDN subscriber downloads or MS Dynamics Customer source etc.

Cleaning up my machines I lost the SDK for Visual Studio add-in creation in Dynamics GP. After trying to web search for it I didn’t find anything but the links to the GP2013 versions.

Looking at the installation media, found it under:

GP2015 R2 MDGP2015_R2_DVD_ENUS\Tools\SDK\VS Tools

Installer for Visual Studio Tools folder for GP 2015R2

Hopefully this post will help someone else to remind them more quickly to check the media for this and other components like eConnect.

The media for GP can be downloaded from MSDN or other locations on the internet.

See this customer source page, you will need to log into your account to access it:

Product Release Downloads for Microsoft Dynamics GP 2015

SQL Calculate Customer Balance & On Order amount for Dynamics GP

The values in the Customer summary table RM00103 keep drifting from what they should be. This causes issues with our pick list printing service that was using them in a complex credit checking routine. Sometimes the on order amount could be minus thousands of pounds, leading to goods being despatched when they shouldn’t.

For the way we use GP, the following script is adequate to compare the values in the tables with the transactions in GP. We don’t use receivable invoices but this could easily be added in using table IVC10100.

So on freshly reconciled company the following script returns no rows, when the values go out of line a SQL job emails the row to us, in order to try and ascertain what just happened somewhere in GP.

I post the SQL here merely as a starting point for others looking at customer balances or outstanding transactions against customers in GP with SQL as I didn’t see much from web searches on that subject. I strongly urge you to test the query first on your system.

;WITH CTE_OrderSummary AS(
CTE_RM_Summary AS(
FROM RM20101

'Summary information differs from calc value for balance or on order amount'

FROM RM00103

test company, test database, live company in test database, test company in live database…

How times have changed. Now we have a SAN and ESX Hypervisors hosting our servers, virtualisation and new hardware platforms have made managing enterprise infrastructure so much easier. For developers this had made possible the cloning of live environments, yes webservers, databases, ERP and various integrations can all be plucked from operational service and plonked down into a test environment.

This means testing and development can take place in environments that are almost identical to production live environments. Importantly I said almost identical – users can log in and test scenarios and perform UAT, performing end to end UAT scripts without interruption. Years ago they would have had to test one section at a time and everyone would pray when it was all stitched together and launched into the production environment that it would hang together. The confidence factor from testing is increased as a consequence of the improved environments and this mostly makes for safer deployments.

As developers we have almost identical copies of the production environments to work with, whilst so helpful It introduces new hazards and risks. We noticed just how much time is spent orientating every conversation, prefacing it with what version of the various systems we are talking about for fear of misunderstanding. Much akin to the header on information packets. It is risky when production and live database are open in SQL server, oh so easy to run a script against the wrong system. I am happy to say I’ve never taken GP down by running SQL against it.. yet, but when we changed to this arrangement I became very aware of how easy it is to make a mistake now. When it was a test company we were developing in, then the connection was obvious. The problem is when both databases look the same including the name and only the server is different then it becomes all to easy to make mistakes. It becomes essential to keep an eye on the connections shown in by hovering over the tabs of the SQL pane.

I also use an add in for SQL server management studio, SSMS Boostthat has recovered many scripts from loss during system crashes or simply discarding a script I wish I hadn't.  It keeps a history of everything in SQL management studio, saved or not and lets the user go back to scripts ran days ago. It also provides search for that history too- highly recommended. Anyway I am off track, this add in also has the facility to colour the connections on the task bar at the bottom. This can be another helpful aid if orientation when working between multiple connections between live and test website and GP databases.



GP Power Tools (Support Debugging Tool)

It is also dangerous for users of GP, using the GP client and developers working between the clone environment and production. Even with explicit warnings about not entering genuine orders into the test system, it only took a day for someone to do it! Luckily it was only one order and not a whole day’s worth.

The GP power tools gives us the ability to colour windows in GP according to the company database it belongs to. This provides a visual cue to the user to instantly recognise which company the user is working in. See the screen shot for the configuration of this feature. I don’t know if the newer power tools is exactly the same, but with this version, it cannot differentiate between the database server the company is running on. Hence we cannot use this program to identify the difference between servers.


Visual Studio Tools

Due to the accidental use of the clone server, it became apparent we needed something to tell the difference easily, when users are logged into GP in the clone environment. We used the same idea of colouring the user interface depending upon the company, but instead used the server name as the identifier. The solution is implementing as part of the Visual Studio Tools plugin we have written. It takes advantage of the same Dexterity calls (Color_SetSystemColor) as shown in the references that follow, pay particular attention to “Colours Depending on” in the VBA example.

We see here how the bar is coloured, this is enough to know we are in danger.


There is still the problems of multiple test and production GP companies, servers and databases, but at least when in GP it is clear which connection is currently in use from the user interface. Thanks to Michael on the team for developing this solution.


Differentiating Companies in Microsoft Dynamics GP

Hybrid – Changing Screen Colours Depending on Company Example