Collation Problem - SQL Server 2005

Question

I would like to understand this error I'm getting on a SQL Server 2005 instance. Error below:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.

Both databases included in a simple query have Latin1 General CI AI, but had SQL Latin1 General CP1 CI AI before. I changed the collation trying to match them but still get the aforementioned error every time I try to join a temp table with a table from a user database. I have no issues creating temp tables.

Answer 1

If you create a temp table and your database has a different collation other than TempDB (which has the same collation as the system - based on installation), then comparisons/lookups/joins - may have problems. A simple trick to get around this is to use database_default:

CREATE TABLE #test2
(
   col1 varchar(12) COLLATE database_default
)
go

Re. Changing Database Collation and dealing with TempDB Objects

Did you rebuild all indexes after changing the Database's collation order?

Answer 2

I've run across very similar-sounding problems when moving a database between servers with different collations. The database comes across with it's original collation, but because the new server's collation (and hence the tempdb database's collation) is different, it causes problems when I create a temp table and try to join with it.

If you're saying that your database's collation is the same as the server's collation, then I guess you must have some columns that are using a different collation. (You can change the collation on a column-by-column basis).

Anyway, I always deal with these problems simply by changing the collation of the whole database to match the target server after restoring it. That's a PITA to do manually, but luckily there's a tool to do it.

Answer 3

From what I recall, if you are using an in-memory tempDb, it gets recreated as needed. Thus, if your Model database has a different collation, a new created db (like tempDb) will inherit it from Model.

Answer 4

Look in the table design that both fields you do the join have the same collation settings.

ALTER TABLE <table>
ALTER COLUMN <column> VARCHAR(200) COLLATE Latin1_General_CI_AI
GO

Answer 5

When you join you can use COLLATE to change the collation on the fly to make them the same:

select c.name, o.orderid from customer as c
inner join order as o on c.custid = o.custid COLLATE SQL_Latin1_General_CP1_CI_AI

Assuming that cust is SQL_Latin1_General_CP1_CI_AI and order is some other collation, the above query will fix the problem.