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

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.

Answer 2

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