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 |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2013-09-12 : 13:41:21
|
| Hi there,I ran into an error of:Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.And I did some search using SELECT col.name, col.collation_nameFROM sys.columns colWHERE object_id = OBJECT_ID('TabelName') and found that all columns collation are SQL_Latin1_General_CP1_CI_AS except Id is null and I ran this also: select * from sys.types it turned out that all data types like char, varchar, text all show SQL_Latin1_General_CP1_CI_AS while others are null. Also I ran select SERVERPROPERTY('collation') it gives Latin_General_CI_AS. Why there are 2 collations in the server. I don't know what was set for installation, maybe Latin1_General_CI_AS. I just need make it work. So any idea how can I change it, Latin or SQL_Latin? I read some article saying I can alter the database with collate clause but would that change my data content?Thanks in advance. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-12 : 13:56:52
|
All of the information you described are normal and expected behavior. Think of the collation as hierarchy. There is a server collation at the top (or root). Then, each database has its own collation. When you create the database, the database inherits the collation of the server. Each object in the database (columns) has its own collation. When you create those columns they inherit the collation of the database. In each case you can specify a different collation.If you restored a database created on one server to a second server with a different collation, the restored database would have different collation that the server collation.Only character type columns are subject to the collation property. So for other columns you would see NULL as the collation property.In your queries, you can force the collation to one or the other. For example:SELECT * FROM YourTableWHERE column1 collate SQL_Latin1_General_CP1_CI_AS = column2 collate SQL_Latin1_General_CP1_CI_AS You can also change the collation of the database (but that won't change the collation of the columns already created. It will affect only columns that you create subsequently).You can also change the collation of the columns, but you would need to do regression testing to make sure that that does not break anything. |
 |
|
|
|
|
|
configuring Collation