Personally I came across a similar issue when I was involved with the transfer of an application from a site in Holland to my office in the UK. It seems that the application had originally been developed in SQL Server v7 and over the years the instance had been upgraded to SQL Server 2000. I believe (I may be wrong here) the default collation settings had changed between versions, thus when I restored the database into a fresh instance of 2000 I started getting these odd collation errors.
What seemed to be happening was that the collation settings were different on the application database as opposed to the tempdb database, which we were using for temporary tables. I had to figure out what the necessary collation setting was for my tempdb, something that unfortunately can't be changed after SQL Server has been installed. Armed with this knowledge I had to reinstall my SQL Server instance with the correct collation settings set up at install time.