SBX - Search With Button

SBX - Forum Post Title

Discordant SQL Server columns collations

Microsoft Dynamics AX Forum

Gino Deschênes asked a question on 2 Feb 2015 2:59 PM
My Badges

Question Status

Unanswered

Hi everybody,

 

I found that even for a completely new AX installation, the AX model et baseline databases have discordant columns collations even for columns belonging a same table.  We followed the recommendation about collations for AX:

 

https://technet.microsoft.com/en-us/library/dd309734.aspx

 

All Microsoft Dynamics AX databases must use the same SQL collation. These databases include the business database, model store database, Microsoft SQL Server Reporting Services database, and Microsoft SQL Server Analysis Services database.

 

One of the collation is the same as the server’s default collation which is Latin1_general_ci_as.  The collation property of every AX database is also latin1_general_ci_as.  But for AX Baseline and AX Model database, some tables have different columns with different collation.  For example with the table dbo.ModelSecurityPolicyTBD, the column ELEMENTNAME have the old SQL collation and all other columns are the same as the database which match also the default server’s collation.

 

dbo.ModelSecurityPolicyTBD .NAME

3

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD .LABEL

4

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.PRIMARYTABLEAOTNAME

5

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.QUERYOBJECTAOTNAME

6

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.DESCRIPTION

7

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.CONTEXTSTRING

9

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.POLICYGROUPNAME

14

Latin1_General_CI_AS

dbo.ModelSecurityPolicyTBD.ELEMENTNAME

18

SQL_Latin1_General_CP1_CI_AS

 

For this other table most of the columns are in the old SQL collation, and only one match database and server collation:

 

dbo.ModelSecurityPermission.OWNERNAME

4

SQL_Latin1_General_CP1_CI_AS

dbo.ModelSecurityPermission.OWNERCHILDNAME

5

SQL_Latin1_General_CP1_CI_AS

dbo.ModelSecurityPermission.RELATIONNAME

7

SQL_Latin1_General_CP1_CI_AS

dbo.ModelSecurityPermission.MANAGEDBY

11

Latin1_General_CI_AS

dbo.ModelSecurityPermission.OBJECTNAME

13

SQL_Latin1_General_CP1_CI_AS

dbo.ModelSecurityPermission.OBJECTCHILDNAME

14

SQL_Latin1_General_CP1_CI_AS

 

The query below list all tables with columns for which collations are not all the same.

 

-- Query that finds collation differences into AX databases

Select

distinct

  dense_rank() over(order by collation_Name) as ordreCollation

, dense_rank() over(partition by collation_Name order by table_schema, table_name) as ordreTable

, collation_name

, TABLE_SCHEMA

, TABLE_NAME

From

  INFORMATION_SCHEMA.COLUMNS

Where collation_name is not null

  and OBJECTPROPERTYEX(object_id(table_schema+'.'+table_name), 'isUserTable')=1

Order by ordreCollation desc

 

 

As a DBA I know that having several collation into a database is running into problems.  Query needs to have the collate clause if there is column comparisons or join doesn’t match columns for which collations are not the same. The collate clause may prevent also index use, making queries less efficient.

 

I wonder if these differences are just overlooked or are properly handled by AX??

 

Reply
Anonymous picture
Anonymous
My Badges

A sentence need to be rephrased in the previous post:

Query needs to have the collate clause if there is column comparisons or join between columns for which collations are not the same.

Reply

SBX - Two Col Forum

SBX - Migrated JS