Setting and Changing the Server Collation

Here is a data warehousing project following by the standard implementation process, Development (DEV) → User Acceptable Test (UAT) → Production (PROD.) DEV and UAT server have been installed MSSQL Server 2005 Developer Edition as default setting which has Latin1_General_CI_AS as default collation, but PROD server has been installed MSSQL Server 2005 Enterprise Edition as SQL_Latin1_General_CP1_CI_AI as default collation. It will bring errors when you do the data migration from DEV/UAT to PROD. The only solution is to change the collation setting in either of them.

You can easily change the collation setting in User Database but not for master database. What you need to do is change the collation in the master database of one of your SQL machines. (Recommend using SQL_Latin1_General_CP1_CI_AS, although it seems not many differences between them.)

Here are some useful links:

Setting and Changing the Server Collation
http://msdn.microsoft.com/en-us/library/ms179254.aspx

Setting and Changing the Database Collation
http://msdn.microsoft.com/en-us/library/ms175835.aspx

Setting and Changing the Column Collation
http://msdn.microsoft.com/en-us/library/ms190920.aspx

How to transfer a database from one collation to another collation in SQL Server
http://support.microsoft.com/kb/325335



If you decide to reinstall a new instance, please beware the “Dictionary order, case-insensitive, for use with 1252 Character Set” is the right one for SQL_Latin1_General_CP1_CI_AS