Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SirRawlins
Starting Member
14 Posts |
 Posted - 2013-05-03 : 07:44:32
|
| Hello All,I have been receiving SQL errors recently about collation:Cannot resolve the collation conflict between "Latin1_General_CI_AS\" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."Looking at the DB, the default collation is set to "SQL_Latin1_General_CP1_CI_AS"However, most of the db was migrated from another instance a couple of months ago and has the collation explicitly defined on the columns as "Latin1_General_CI_AS"Newly added columns are using the database default, which is where the conflict has occurred.Can someone please give me some advice on how to alter things so they all match up nicely - will simple changing the default database collation to match the explicitly defined columns be enough? Or will this not effect data which has already been inserted into those columns?Appreciate any advice, I am not a DBA, so please bare in mind I'm a complete numpty with this stuff.Thanks,Rob |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-03 : 08:56:26
|
| Perhaps the simplest way to think about collations as a hierarchy that exists on server, database and columns (and even finer levels such as in an expression or variable).At each level, there is a default collation. When you create an object under that hierarchy, unless you specify an explicit collation, it inherits the collation of the parent.So for example, if you create a table in a database that has character columns, unless you specify the collation of the columns, they all inherity the collation of the database.The second thing to keep in mind is that you can change the collation of any object in the hierarchy, but that will not change the collation of the sub-objects already created. Any new objects you create will inherit the new collation.Given those two facts what you would need to do is to change the collation of the database and all the columns that are not already in the right collation, to the collation that you want them to be. It is not as hard as it sounds - go to this page and look at the links there: http://msdn.microsoft.com/en-us/library/ms177439(v=sql.105).aspx , do some experimentation on a test table/database. You can query for all the collations and create one script that will alter the collations. The hard part, and you need to be very careful with this, is that you need to test everything to make sure that you are not breaking any existing code OR data. |
 |
|
|
|
|
|
|
|
Correcting Collation Issue