How to collate SQL_Latin1_General_CP1_CI_AS using IN operator

Question

I want to filter records on 'Email' my query is like this.

SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer)

It gives me 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 can use collate if there is equal operator (=) instead of IN. But using Collate here gives me syntax error.

SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer) collate SQL_Latin1_General_CP1_CI_AS

How can I resolve this. I can't drop and re create tables because it is live db.

Data in #temp table is from SQL Server 2000 and Customer table is in SQL Server 2005

Thanks.

Answer 1

...
NOT IN (SELECT email COLLATE SQL_Latin1_General_CP1_CI_AS AS email FROM Customer)

The collate bit follows the column name, basically.

Or this (it depends on which one you want):

SELECT * FROM #temp WHERE email collate SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT email FROM Customer) 

Finally, if your DB is SQL_Latin1_General_CP1_CI_AS, one option is to use ...COLLATE Database_Default... to coerce to a default setting.