Database collation different from tempdb collation

I just received the source code and database of an application from my client (it was developed by another company, from a different country) and the application is throwing some exceptions related to the objects collation:


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

I saw that in my case this is caused when a Stored Procedure creates a #temp table and this #temp table is used in a comparison with tables from the application (the application database uses SQL_Latin1_General_CP1_CI_AS and tempdb uses Latin1_General_CI_AS).

I'm adding COLLATE SQL_Latin1_General_CP1_CI_AS to the CREATE TABLE #TEMPTABLE statement, however this database has many Stored Procedures that may use #temp tables.

How can I fix this faster without breaking the other databases/applications?

Does this database need to be on this Instance? If not, then as @Max Vernon suggested in a comment on the question, you could install this on an Instance that is created with a default Collation of SQL_Latin1_General_CP1_CI_AS. Perhaps you could even get away with using SQL Server Express Edition, if the database is

• They treat apostrophes and dashes within words differently

• The non-SQL_ Collations allow for some expansions / normalization offered by Unicode that will equate more characters than the SQL_ Collations.

StackExchange Database Administrators Q#161012, answer score: 5