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??

Solution 1:

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