Much to my chagrin, discovered that the collation of the TempDB and user databases in an instance of SQL Server I am working on are different. The TempDB is Latin1_General_CI_AS and the user databases are SQL_Latin1_General_CP1_CI_AS. This causes a problem when joining a table copied in from one of the user databases to TempDB and a table created in TempDB.
One solution is to change all user databases to Latin1_General_CI_AS, but I am not sure what implication this will have? Kimberly L. Tripp has an excellent post explaining the implications in more detail so will have to consult this for more info
SQL Server
collation