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
AX2012PROD	Latin1_General_CI_AS
master	SQL_Latin1_General_CP1_CI_AS
model	SQL_Latin1_General_CP1_CI_AS
msdb	SQL_Latin1_General_CP1_CI_AS
tempdb	SQL_Latin1_General_CP1_CI_AS

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 (http://technet.microsoft.com/en-us/library/dd207003.aspx).

 

More information can be found here:

http://technet.microsoft.com/en-us/library/bb402915.aspx
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