An End to Collation Blues

Collation in SQL Server 2000 is oftern a minefield.
The collation must exactly match on a join or the query will fail if both fields have a defined
collation.

There are lots of absolutely identical collations (for example the default UK and US settings are identical yet incompatible).

There is no easy way of correcting the databases collation other than using DTS to completely copy the database.

The following is the solution to all collation blues:

COLLATE database_default
This needs to be after the appropriate join :

    select a
    from foo
    where bar1 = bar2 COLLATE database_default

This eliminates the problem.