Convert table collation

Sometimes when performing a join operation between two tables with different collations you might come across an error like this:

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

A method to work around this is to make all tables using the same collation. However, modify a collation usually requires a drop and re-create the table itself.

Well, we can use this query to modify the collation of a table on the fly:

    USE AdventureWorks2008
    GO
    ALTER TABLE dbo.Person
    ALTER COLUMN FirstName
    NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL

Run this to the column(s) having problem.
P.S: Latin1_General_CI_AS  is a default database collation in SQL Server.