SageCRM and SQL Collation setup

By | March 6, 2014

While installing older versions of CRM i.e. v7.1 Sp1 or below the installation process in background checks the SQL Server collation defined at database server and accordingly it maps the same collation to CRM database. If the collation at SQL level is not defined correctly same reflects to CRM database as well. Because of this CRM can experience problems after the installation or installation will fail eventually.
New Stuff: Creating tasks through workflow with defined due period
Considering this I have set process in my Implementation team to check SQL server collation before actually starting the installation of SageCRM.
Nowadays, things are becoming easier and SageCRM has adapted a new way of defining the SQL collation. From 7.1 SP2 onwards, installation process doesn’t check the collation defined at SQL server level. Instead of this, it creates its own collation on CRM database.
Below are some of the default collations:
• For EN Installations = Latin1_General_CI_AS
• For DE Installations = Latin1_General_CI_AS
• For ES Installations = Modern_Spanish_CI_AS
• For FR Installations = French_CI_AS
• For CS Installations = Chinese_PRC_CI_AS
These are the default installation collations that are put in place for the languages outlined above.
Problem Scenario:
Recently, one of my users has reported a problem that I am not able to access CRM as login screen shows below error message.

IMG

This particular issue occurs when connection with SQL is not established properly, SQL service is not running, Password for SQL SA user has been changed, etc… These are some of the issues which we can troubleshoot to make CRM up and running. But, this time it was something else which was very much new for me to give resolution.
The error which was appearing in SQL logs was as below “Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “Latin1_General_CI_AI” in the equal to operation”. 
On checking the current collation defined on the CRM database I found it incorrect. So by changing the collation to Latin1_General_CI_AS solved the issue.
Also Read:
1. How log files are maintained in Sage CRM
2. You may need to recreate views manually – II
3. How to identify and backup correct Sage CRM Database
4. Login Error in Sage CRM
5. You may need to recreate views manually