Over the last few years as we have grown the ERP Practice at Perfect Image we have taken over a number of clients from other Microsoft Dynamics GP partners, both in the UK and elsewhere. Far too many of them have had Dynamics GP installed with the incorrect collation.
According to the system requirements, Microsoft Dynamics GP is supported with two collations:
- SQL_Latin1_General_CP1_CI_AS (the recommend one as it is case insensitive).
- Latin1_General_BIN
Whenever I install SQL Server, I am careful to ensure that I pick the SQL_Latin1_General_CP1_CI_AS collation which is supported; this is important to do, as a UK language server has Latin1_General_CI_AS as the default and you need to proactively make the change. A US server has the correct collation by default.
If I’m honest, I couldn’t have told you why this was important, but it is something I have always been very careful to do. I couldn’t have told you why this was important, until today that is, when I read a guest post by Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, on the Winthrop DC blog.
The post by Craig explains why it is important to use the correct collation. Short version is that while both SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS are 1252 character collations, they handle the Þ character different which can cause incorrect data to be returned in the Reporting Service Reports; there could also be other places where this manifests.
I’d encourage everyone (especially consultants) to read and understand the post so we can stop clients having an incorrect collation installed on the SQL Server used with Microsoft Dynamics GP.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.