Multiple collations need not be an issue - it all depends on how the applications are written. SQL Server is happy to use as many collations as you can imagine.

In my experience, an issue arises when, for instance, temporary tables are created without specifying the collation of the columns. When you do not specify the collation for temporary tables, columns in that table get the same collation as Tempdb. If that is different to the columns in the user database AND you try to compare value (such as in a ON OR a WHERE condition), without specifying the collation to use for the comparison, then you get an error.

The following should demonstrate the issue...

CREATE TABLE dbo.TEST

(Column1 VARCHAR (10) Collate LATIN1_GENERAL_CI_AS)

go

CREATE TABLE #TEMP

(Column1 VARCHAR (10) Collate sql_latin1_general_cp1_ci_as)

SELECT * FROM test t1

inner join #Temp t2

on t1.Column1 = t2.Column1 /* collate LATIN1_GENERAL_CI_AS */

If you un-comment the last bit of the join condition, the query will run properly