Tony Rogerson has an interesting article about Collations in SQL Server, examples and restrictions, explaining how and why they happen and what they mean. This is a problem I've faced again and again, especially when bringing back customer's data. One of the slight annoyances I have with Visual Studio 2003 is that when generating create scripts for tables it puts the collation into the table creation scripts, so I often do a search of the database for COLLATE and remove them, taking the code from

CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

to

CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]

We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default