SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Correcting Collation Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SirRawlins
Starting Member

14 Posts

Posted - 05/03/2013 :  07:44:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3720 Posts

Posted - 05/03/2013 :  08:56:26  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000