SQL Server 2005 Collation conflict

Ever been faced with this error message?

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

This arises from different databases being built on diffferent versions of SQL Server by different developers and when you wanht to make them talk to each other one of them says something like "I've no idea what he's on about, slap a Babel Fish in my ear and let's try again". The Babel Fish in question is a simple piece of code.

Usually this works: COLLATE database_default but sometimes for me I need to use COLLATE Latin1_General_CS_AS depending on what systems i'm working with.

Where is it used? Here:

SELECT
a.fieldname1
,a.fieldname2
,b.fieldname3
FROM table1 a
INNER JOIN table2 b ON a.id=b.id COLLATE database_default
WHERE id = 1

In case you didn't spot it, its sitting on the end of the INNER JOIN line. Sometimes you'll just have to experiment with where it goes. If you're using an inline query, it'll go in there. If you engaging in some complicated WHERE clause fruitiness, then it may need to go in there. Wherever it goes, its your saviour.