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
 General SQL Server Forums
 New to SQL Server Programming
 configuring Collation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

247 Posts

Posted - 09/12/2013 :  13:41:21  Show Profile  Reply with Quote
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_name
FROM
sys.columns col
WHERE
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.

Edited by - allan8964 on 09/12/2013 13:46:25

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/12/2013 :  13:56:52  Show Profile  Reply with Quote
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 YourTable
WHERE 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.
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.04 seconds. Powered By: Snitz Forums 2000