I wrote a query this morning to detect if there are any values in a certain table IN_DumpReason in my SQL Server 2005 database that are not present in somebody else's SQL Server 2000 database:

select *
from IN_DumpReason
WHERE DumpReasonCode NOT IN (
    select reason_id
    from highjump.AAD.dbo.t_reason )

I received the following error message:

"Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

Huh! After some Googling I found that you can tell SQL Server what collation to use when doing an equals test. Use the syntax

ColName COLLATE  CollationName

So which column was which collation above? An "sp_help IN_DumpReason" told me this:

So it would seem my column was the SQL_Latin1_General_CP1_CI_AS, and the other needed to be compared using that collation. I rewrote my query like this:

select *
from IN_DumpReason
WHERE DumpReasonCode NOT IN (
    select reason_id COLLATE SQL_Latin1_General_CP1_CI_AS
    from highjump.AAD.dbo.t_reason )

And now it worked!

You can read more about SQL Server collations here.