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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.