Msg 468 Level 16 State 9 Cannot resolve the collation conflict

Este error se produce cuando en determinados selects hay campos con diferentes collations (colaciones u ordenamientos) y provocan una incompatibilidad al momento de resolver la consulta.
Esto se soluciona especificando de manera concreta la collation con la sentencia collate, veamos el ejemplo a continuación usando la base AdventureWorks.

select addressline1 collate SQL_Latin1_General_CP1_CI_AS from Person.Address
union all
select FirstName collate SQL_Latin1_General_CP1_CI_AS from Person.Contact

Y con la función fn_helpcollations podemos recuperar una lista de collations compatibles con el sistema operativo del server.

select * from fn_helpcollations()

La collation especificada para las bases de datos, campos de tabla especifica la forma en que SQL va a ordenar los campos al momento de presentarlos en pantalla.