Link to home
Start Free TrialLog in
Avatar of MezzutOzil
MezzutOzil

asked on

How the different in SQL Collation affect the application operation? How to resolve?

This is using few MS SQL 2008 R2 servers. There are 4 servers altogether. By default, the collation is set with "Latin1_General_CI_AI". Some how, there is one SQL server was found to set with SQL_Latin1_General_CP1_CI_AS. Do they matched?

My manager doesn't feel comfortable with the difference, and change is a MUST. How should we go and make the changes? Does change the collation directly is the way to go? Any side-effect in doing so? What would be the better approach as all the above 4 servers are in production? Hope any changes done can minimize the impact on production.

Thanks in advance
Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

2 questions for you, do you ever run cross/server queries? And what collation is the database on the server which is SQL_Latin...?

Those collations are not the same and if you try and run a query which joins data from one collation with the other then you will get issues. The *normal* issue people face is the server is one collation and a database another, which is fine until you do a query which uses tempdb and then you get a collation conflict.

However assuming your database is the same collation as the server, and assuming you don't do any cross server queries you will probably be OK.

This explains how to change it if you need to http://msdn.microsoft.com/en-us/library/ms179254.aspx

Whether you need to script your databases depends on whether their collation needs to change or not.

If you can't script the database and need to change the collation then I think it gets tricky. http://msdn.microsoft.com/en-us/library/ms175835(v=sql.100).aspx

Hope that helps.
Avatar of EvilPostIt
By the looks of things Accent Sensitivity is turned on only on the SQL_Latin1 collation. This could mean that you searches act differently if you use accents in the database.

If you restore the database the collation is kept and if the collation on the server is different different this may also cause issues.
ASKER CERTIFIED SOLUTION
Avatar of Mlanda T
Mlanda T
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MezzutOzil
MezzutOzil

ASKER

I'm not SQL expert, just a normal DB admins. My SQL developer has successfully changed the collation and now the sql is working fine. Many thanks for your explanation.