Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot Resolve Collation Conflict

I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

I changed the collation on the database using

ALTER DATABASE [optimiser] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE [optimiser] COLLATE SQL_Latin1_General_CP1_CI_AS ALTER DATABASE [optimiser] SET MULTI_USER 

But I still get the error whenever the stored procedures run. I believe because the SP is using a join to another database (GE's ihistorian) and it has a collation mismatch. IS there anyway to resolve this.

On the old server DB1 was set as Latin1_General_CI_AS and this works fine. The new location for the DB has a default of SQL_Latin1_General_CP1_CI_AS. Is it worth changing the collation n DB1 on the new server back to Latin1_General_CI_AS??

like image 975
Silentbob Avatar asked Jul 12 '13 13:07

Silentbob


People also ask

How do you solve Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS in the equal to operation?

Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” Simply apply the default collation to the fields you are comparing.

How do I fix collation conflict in SQL Server query?

To work around this issue, do one of following: Downgrade from SQL Server 2012 SP3 to Cumulative Update 4 (build 11.00. 5569) for SQL Server 2012 Service Pack 2. Change the collations of the involved databases to be the same as the server collation on Azure SQL Database (SQL_Latin1_General_CP1_CI_AS).

Is SQL_Latin1_General_CP1_CI_AS the same as Latin1_General_CI_AS?

The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

What does it mean by the As in the default collation SQL_Latin1_General_CP1_CI_AS?

If we apply a case sensitive clause to a column, then for example, 'a' and 'A', will be different. But in the case of case insensitive, irrespective of any character or string, it will work. By default, the collate clause will take SQL_Latin1_General_CP1_CI_AS (case insensitive).


1 Answers

The thing about collations is that although the database has its own collation, every table, and every column can have its own collation. If not specified it takes the default of its parent object, but can be different.

When you change collation of the database, it will be the new default for all new tables and columns, but it doesn't change the collation of existing objects inside the database. You have to go and change manually the collation of every table and column.

Luckily there are scripts available on the internet that can do the job. I am not going to recommend any as I haven't tried them but here are few links:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

Update Collation of all fields in database on the fly

http://www.sqlservercentral.com/Forums/Topic820675-146-1.aspx

If you need to have different collation on two objects or can't change collations - you can still JOIN between them using COLLATE command, and choosing the collation you want for join.

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE Latin1_General_CI_AS  

or using default database collation:

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE DATABASE_DEFAULT 
like image 129
Nenad Zivkovic Avatar answered Sep 22 '22 19:09

Nenad Zivkovic