Friday, November 30, 2012

AX 2012 - SQL Server Collation

There is a difference in collation setting between SQL Server default collation setting used for system databases (Master, Model, Msdb, TempDB) and the Microsoft Dynamics AX database . Latin1_General_CI_AS_KS_WS  v.s. Latin1_General_CI_AS


Database Name
Collation name

It is recommended that the collations of user-defined databases match the collation of master or model. Otherwise, collation conflicts can occur that might prevent code from executing. For example, when a stored procedure joins one table to a temporary table, SQL Server might end the batch and return a collation conflict error if the collations of the user-defined database and the model database are different. This occurs because temporary tables are created in TempDB, which bases its collation on that of model.


If you experience collation conflict errors, consider one of the following solutions:

·        Export the data from the user database and import it into new tables that have the same collation as the master and model databases.

·        Rebuild the system databases to use a collation that matches the user database collation. For more information about how to rebuild the system databases, see Rebuilding System Databases (


More information can be found here:
Windows Collation Name (Transact-SQL)

Here you can find a good post that explain the difference between collation Latin1_General_CI_AS (United Kingdom) respect SQL_Latin1_General_CP1_CI_AS (United States).
It’s better to use Latin1_General_CI_AS collation because is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS



Alex P said...

Changing collation on AX 2012 R2 is not supported anymore by Microsoft. I was running into ciphering issue regarding stored procedure while trying to change the database collation.

Tommy Skaue said...

How huge a problem is this for AX2012? Does it have any performance implications?

Denis Macchinetti said...

Hi Tommy

As long you use the same collation for SQL Server default and Dynamics AX database there is no issue with using the Windows collation Latin1_General_CI_AS instead of the SQL Server collation SQL_Latin1_General_CP1_CI_AS but don’t mix them.

Also, read the article below.
On the conclusion you can read : ...Mixing the collations at column, database or instance level could impact on your performance...


erp system said...

Thank you for describing a problem with SQL Server collation in Microsoft Dynamics AX and showing the difference in collation setting between SQL Server default collation setting used for system databases. It was interesting for me!

microsoft dynamics partners said...

Hi guys! Thank you that you wrote a lot interesting information about Microsoft Dynamics AX and showing how to describing a problem with SQL Server! It’s very helping me, because I'm a beginner :) and it was very interesting for me.