SQL Collation – SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS by Craig Verster via WinthropDC

Microsoft Dynamics GPOver 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 a href=’https://winthropdc.wordpress.com/’>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.

SQL Script To Locate Columns With Different Collation

Microsoft SQL ServerA while ago I posted about a problem with a collation conflict on a couple of columns in the Tax table. It seems I posted about how to fix the problem, but it seems I didn’t post how I found the problem columns.

I did this with a fairly simple SQL script:

DECLARE @Collation SYSNAME SET @Collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT
   TABLE_NAME AS 'Table'
   ,COLUMN_NAME AS 'Column'
   ,DATA_TYPE AS 'Data Type'
   ,COLLATION_NAME AS 'Collation Name'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   DATA_TYPE IN ('varchar','char','nvarchar','nchar','text','ntext')
AND
   COLLATION_NAME <> @Collation

Collation Conflict Printing The Tax Detail Report

Microsoft Dynamics GPWe upgraded a client from Microsoft Dynamics GP 9 to Microsoft Dynamics GP 2010 recently, and the first time they tried to run the VAT 100 report they received a number of errors:

Microsoft Dynamics GP - An error occurred executing SQL statements.Microsoft Dynamics GP – An error occurred executing SQL statements.

Continue reading “Collation Conflict Printing The Tax Detail Report”