Yes it was probably overlooked when the database was created on the other server. Personally, I would avoid having to explicitly specify a collation for a column in my query because it's sort of introducing a concept into the query which the query really shouldn't know about. But that's just a personal preference, I didn't say it's what you ought to be doing.

The default in SQL 2005 is SQL_Latin1_General_CI_AS. Any collation type that starts with SQL_* is compatible with SQL 2000, so that helps when you've been migrating. You're probably getting your issue because the temporary table is defaulting to SQL 2005's non-compatible default which is Latin1_General_CI_AI. Because it's a migrated database, I'd think that everytime you create a temporary table, you need to explicitly define its collation to be SQL_*.

The option to set the collation should be available to you in the database properties box when you create the database or edit its properties.