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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s