Hands On With Microsoft Dynamics GP 2015 R2: Install System Database

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

The second post in this series, covers the installation of the System database. This is the database which holds the deployed companies and security configuration amongst oither items. To install the System database, run GP Utilities from the Start screen.

Despite what seems to be a regular argument I seem to have with Infrastructure people, you must log into GP Utilities using the sa account in order to be able to install the System database (no “ifs”, no “buts”; it must be sa):

Welcome to Microsoft Dynamics GP Utilities

Continue reading “Hands On With Microsoft Dynamics GP 2015 R2: Install System Database”

Hands On With Microsoft Dynamics GP 2015 R2: Install The Client

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

The first post covers the installation of the client. I am doing this installation on a brand new virtual machine built specially for this series of posts; if you are using an existing machine then the prerequisite installation may be somewhat different.

To install the client, run the setup.exe on the downloaded installation files. This will launch the Microsoft Dynamics GP 2015 Bootstrapper Setup: Click Install to begin the installation of the prerequisites:

Microsoft Dynamics GP 2015 Bootstrapper Setup

Continue reading “Hands On With Microsoft Dynamics GP 2015 R2: Install The Client”

Hands On With Microsoft Dynamics GP 2015 R2: Series Index

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 was released at the end of May. As I mentioned at the time, I was away from home for work and rather busy otherwise so this series of posts has been somewhat delayed.

However, I am now getting my hands on GP 2015 R2 and thought I would do my usual set of posts on the installation of the various components and also the usage of the new functionality. This is going to be quite a long series of posts, so keep checking back regularly.

Continue reading “Hands On With Microsoft Dynamics GP 2015 R2: Series Index”

Update EU Member Country Code Information

Microsoft Dynamics GPThey say there are only two certainties in life; death and taxes. Well, one other certainty is that time passes and with the passage of time comes change.

One of the changes is that the European Union has become larger, but the VAT10001 (VAT Country Code MSTR) in Microsoft Dynamics GP which holds the country code information has not been updated.

As the information in this table has a direct impact on clients, I created a script to flag the missing current EU Member states (Croatia, Finland and Hungary):

UPDATE
	VAT10001
SET
	ECFLAG = 1
WHERE
	CCode IN ('HR' --Croatia
	,'FI' --Finland
	,'HU') --Hungary
GO

As always have a good backup of your databases before running the script and check afterwards that everything is good.

The Future Of The Support Debugging Tool

Winthrop DCI am slightly behind the times with this post as this news is a few weeks old, but I have been busy and am now trying to catch up. When David Musgrave was working for Microsoft he wrote the Support Debugging Tool which contained some very useful functions. I did wonder what the future held for it when he left Microsoft last year, but he posted a while ago that he had negotiated an exclusive agreement with Microsoft which allows him to continue work on and release the tool.

There will be some changed to the Support Debugging Tool under this agreement. Most noticeable is the fact that it will now be called GP Power Tools.

GP Power Tools will be initially released for the following Microsoft Dynamics GP versions:

  • v11.0: Microsoft Dynamics GP 2010
  • v12.0: Microsoft Dynamics GP 2013 and GP 2013 R2
  • v14.0: Microsoft Dynamics GP 2015

There is going to be some changes to the functionality when GP Power Tools is launched:

  • New simpler Navigation with menus and area page
  • Database Validation, to ensure that your upgrades work
  • Numerous enhancements and the odd bug fix
  • And lots more….

Another change is that GP Power Tools will now be available via an annual subscription for each customer site at the special introductory price of US$365.00. That’s a dollar a day, and every four years you will get a day for free.

For now continue to use the free Support Debugging Tool for Microsoft Dynamics GP 2010 and GP 2013 (inc. GP 2013 R2) which is available from http://winthropdc.com/SDT.

Stay tuned here or to the WInthrop DC blog for more information on when to upgrade to GP Power Tools for continued support and improved functionality.

Those of you on Microsoft Dynamics GP 2015 will need to be patient for a while longer and wait for the release of GP Power Tools.

Real-world Business Intelligence with Microsoft Dynamics GP Available Now

Microsoft Dynamics GPI mentioned a couple of months ago that there was a new book coming for MIcrosoft Dynamics GP (and it wasn’t my new Workflow 2.0 book.

Well, that book is Real-world Business Intelligence with Microsoft Dynamics GP by Dynamics GP MVPs Belinda Allen and Mark Polino:

Real-world Business Intelligence with Microsoft Dynamics GP

Continue reading “Real-world Business Intelligence with Microsoft Dynamics GP Available Now”

Microsoft Dynamics GP Workflow 2.0 Available In Mobi and ePub Formats

Microsoft Dynamics GPI recently released my fourth book Microsoft Dynamics GP Workflow 2.0.

Initially it was available only as an eBook in PDF format, but now the paperback format is available from Amazon UK and Amazon US.

I have also added Mobi and ePub formats to the eBook download on azurecurve Publishing. If you have previously bought the eBook and want the new formats, send me an email with the email address used for the original order and I’ll despatch the new formats as soon as I am able.

eBook Paperback
azurecurve Publishing Amazon.com Amazon.co.uk

The ebook will also become available from other retailers too (including the Apple Store) in a few days once it has passed through the approval process.

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks & Operations

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasksAndOperations TO DYNGRP
GO

Security Views For Use In SmartList Designer: Security Tasks & Operations

Microsoft Dynamics GPThe seventh SQL view in this series does not include users, but instead shows the tasks and the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_AZRCRV_SecurityTasksWithOperations] AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['Security Task Master'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Task Master'].SECURITYTASKDESC AS 'Security Task Description'
	,['Security Resource Descriptions'].PRODNAME AS 'Product Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].TYPESTR AS 'Security Operation Type'
FROM
	SY09000 AS ['Security Task Master']
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON
			['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON
			['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
		AND
			['Security Resource Descriptions'].DICTID = ['Security Assignment Task Operations'].DICTID
GO

GRANT SELECT ON uv_AZRCRV_SecurityTasksWithOperations TO DYNGRP
GO