How to collate SQL_Latin1_General_CP1_CI_AS using IN operator
sql-server, sql-server-2005, t-sql
Solution
...
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.
Problem
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.