Hands On with Microsoft Dynamics GP October 2019 Release: System Requirements

Microsoft Dynamics GPThis post is part of the Hands On with Microsoft Dynamics GP October 2019 Release series where I am going hands on with the new version of Microsoft Dynamics GP.

The October 2019 release of Microsoft Dynamics GP largely has the same system requirements as Microsoft Dynamics GP 2018 R2 with the addition of the following:

  • Windows Server 2019
  • SQL Server 2019
  • Office 2019

SSRS requires that you use local SQL Server relational databases… error

Microsoft Dynamics GPMicrosoft Dynamics GP ships with a set of default reports which can be deployed into SQL Server Reporting Services; before doing the deploy, there is a setting in the SSRS web.config file which needs to be changed to avoid a maximum request length error.

Recently, when performing an upgrade of a clients system to Microsoft Dynamics GP 23018 R2, the deployment of the reports went fine, but we encountered a problem while testing the deployed reports:

SSRS reports error message

The client had a licenced version of SQL Server 2018 Web Edition which worked in all other respects; the SSRS service was running locally on the SQL Server which was hosting the databases for both SSRS and Microsoft Dynamics GP itself.

This wasn’t something I’ve encountered before, but, fortunately, a colleague had. The problem is not with the Web Edition itself, but rather with the data connection deployed by Microsoft Dynamics GP for the reports. When the data connections are deployed, there is a space created preceding the Data Source name:

Connection settings in SSRS Manager

The fix is to go through all of the datab connections and remove the space after the equals sign, so that, in the example above, Data Source= IG-SQL2017-01\GP... becomes Data Source=IG-SQL2017-01\GP....

Cannot Launch SQL Server Management Studio

Microsoft SQL ServerI work on a variety of test and demo systems and recently stumbled across an oddity with SQL Server Management Studio (SSMS); when I tried to launch it using the shortcut, the splash screen would display, stay on screen for a few seconds before disappearing with SSMS never starting, but no error message appearing or showing in the logs.

If I tried to start it by typing ssms in a command prompt I received the following error:

ssms error


Windows cannot find 'ssms'. Make sure you rtyped the name correctly, and then try again.

I did some exploring and found this article on Stackoverflow which resolved my problem.

Find the Ssms.exe.config file and erase the line <NgenBind_OptimizeNonGac enabled="1" />:

Noterpad.exe showing Ssms.exe.config

SQL Stored Procedure to Generate Sequential Number

Microsoft SQL ServerWhile much of the work I do is directly with Microsoft Dynamics GP, I also do work for clients which isn’t directly related. I’ve created code to generate numbers a few times in the past and figured I might as well post the base code I use for this to make it easier to find in future.

I’ve created it in such a way that several unique numbers can be stored and incremented.

The first part of the code creates a table to hold the number type and next number:

-- drop table if it exists
-- create table

Next, I create a stored procedure which will increment and return the next number:

-- drop stored proc if it exists
-- create stored proc
CREATE PROCEDURE [dbo].[usp_AZRCRV_GetNextNumber]
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). */
SET NOCOUNT ON BEGIN TRAN -- if this is the first value generated for this table, start with one IF NOT EXISTS (SELECT * FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE) INSERT INTO ut_AZRCRV_NextNumber (NMBRTYPE,NEXTNMBR) VALUES (@NMBRTYPE,1) -- select next number from table into variable SELECT @NEXTNMBR = NEXTNMBR FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE -- increment number by 1 UPDATE ut_AZRCRV_NextNumber SET NEXTNMBR = NEXTNMBR + 1 WHERE NMBRTYPE = @NMBRTYPE COMMIT TRAN -- return variable containing next number RETURN @NEXTNMBR GO

Then, I grant execute permissions to the relevant database role:

-- grant execute permission on stored proc to ur_AZRCRV_InvoiceUser
GRANT EXECUTE ON usp_AZRCRV_GetNextNumber TO ur_AZRCRV_InvoiceUser

And finally, I have the SQL code which will generate the next number:

-- code to get next number



SQL Function to Remove Alphanumeric Characters

Microsoft Dynamics GPI created a customisation recently for a client which would generate a Vendor ID based on the name, by removing alphanumeric characters. In order to make it as flexible as possible, I created the function to accept a parameter for type which will cause the function to strip different characters:

  • A – leaves alpha characters only.
  • N – leaves numeric characters only.
  • AN – leaves alphanumeric characters.

The second parameter is the string which should have the characters stripped:

IF OBJECT_ID (N'uv_AZRCRV_StripCharacters', N'FN') IS NOT NULL
	DROP FUNCTION uv_AZRCRV_StripCharacters

CREATE FUNCTION uv_AZRCRV_StripCharacters(@Type VARCHAR(100), @String VARCHAR(MAX))
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).
	IF (@Type = 'Alpha' OR @Type = 'A')
		SET @PatIndex = '%[^a-z]%'
	IF (@Type = 'Numeric' OR @Type = 'N')
		SET @PatIndex = '%[^0-9]%'
	IF (@Type = 'AlphaNumeric' OR @Type = 'AN')
		SET @PatIndex = '%[^a-z0-9]%'

	WHILE PATINDEX(@PatIndex, @String) < 0
		SET @String = STUFF(@String, PATINDEX(@PatIndex, @String), 1, '')

	RETURN @String


Can I Create a Temp Table With Collation Different to the Server Collation?

Microsoft SQL ServerAs with virtually all headlines containing a question, the short answer is no. From the research I have done, the temp table will always be created with the collation of the SQL Server. If you want to skip to the solution, click here.

Why would you want a different collation? Well, in this case we need a different collation as there is a third party database which a customisation in Microsoft Dynamics GP is using for pricing lookups. The database was originally on a server with the same collation (SQL_Latin1_General_CP1_CI_AS) as the Dynamics GP server (and which the database shares). However, the database has now been moved to a new server with the default UK server collation (Latin1_General_CI_AS).

The problem now is that when data is being inserted or updated into this database, a collation error is produced:

Cannot resolve the collation conflict

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal operation.

Code: 8004OE14

Source: Microsoft OLE DB Provider for ODBC Drivers

Continue reading “Can I Create a Temp Table With Collation Different to the Server Collation?”

Create User or Assign Company Access Without Using sa

Microsoft Dynamics GPWith Microsoft Dynamics GP, there are only two user accounts which can, by default, create new users or assign access to companies; these the the sa (SQL Server System Administrator) and DYNSA (Dynamics GP System Administrator).

The former account should only be used when absolutely necessary (such as when implementing Microsoft Dynamics GP or moving it to a new SQL Server Instance; there are some ISV products which also insist on the sa account when it isn’t strictly necessary from a tecHnical perspective).

The recommended way of maintaining security is to configure a normal user account with the permissions necessary to create and assign users to companies. There are a few steps to go through to assign the relevant security.

Mark Polino did a post a while ago on adding users without using the sa account, but, in this post, Mark assigned the sysadmin role to the user. While this will do the job, and in fewer steps, I prefer to lock down security so users only have the permissions required, which precludes assigning a sysadmin role. The reason for this is both best practice, but also that I have several clients who will not allow the sysadmin role to be assigned to a GP user.

The following steps cover the minimum security required for a user to be able to add new users or assign them access to companies.

Assign the user to all companies in Microsoft Dynamics GP (this is done in the User Access Setup window (Administration area page » Setup » System » User Access):

User Access Setup

Continue reading “Create User or Assign Company Access Without Using sa”

Formatting SQL

Microsoft SQL ServerI create a fair amount of T-SQL in the form of selects, views and stored procedures. While I try to format my code to make it easy to use, I do encounter code from other people which is not very well formatted; and if you look at the stored procedures or views which ship with Microsoft Dynamics GP, they are generally formatted with all of the code on a single line.

There are a variety of ways of formatting SQL and I’m sure there are addins for SQL Server Management Studio; however, I often work on clients servers and can’t just install anything I want.

Therefore, a web based alternative is favourite and SQLFormat is the best one I have come across so far.

To use, simple paste your code into the big box and click the Format SQL button:


You can even load a file should you wish.

I’m posting it here as a reminder to myself.

How to Install Microsoft SQL Server 2017: Download SSRS 2017

Microsoft SQL ServerIn this short series of posts, I am going to be covering the installation of Microsoft SQL Server 2017; the installation process for this version is somewhat different to previous ones, in that SQL Server Management Studio and SQL Server Reporting Services have been separated out from the main installer.

The series index can be found here. I am not a SQL expert and this series of posts is intended only for use on demo or test environment and not production ones.

This is a very late addition to this series of posts; I recently came to install SQL Server 2017 on a new machine which did not have an Internet connection and so needed to download the installer and copy itto the new machine.

In the installation post I used the download option in the installer, but didn’t include the direct download link.

Well, to remedy that, here is the download link to the latest version of SSRS 2017.

SQL Snippet: Get Dates for Accruals

Microsoft SQL ServerAs I’ve mentioned before I write a fair bit of SQL code for various projects or support calls and will be posting some of it here.

I recently created a report for a client to use to extract transaction lines to use to import as an accruals journal; as part of the extract I worked out the last day of the one month and the first day of the next to use as the transaction and reversing dates on the journal.

The scripts below has versions for both before and after SQL 2012 (with the introduction of the EOMONTH function in 2012, getting these dates became easier).

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).
-- set date variable

-- get last date of this month
SELECT CONVERT(VARCHAR(10), DATEADD(month, ((YEAR(@Date) - 1900) * 12) + month(@Date), -1), 126)

-- get last date of this month in SQL 2012

-- get first date of next month
SELECT CONVERT(VARCHAR(10), DATEADD(month, DATEDIFF(month, 1, DATEADD( month, 1, @Date )), 0), 126)

-- get first date of next month in SQL 2012