Cannot resolve the collation conflict between this and that

I have data from 2 different databases, which i need to consolidate and compare which each other.

An example:

select distinct CustomerNo from DB1.dbo.TableCustomers
UNION ALL
select distinct CustNo from DB2.dbo.NewTableCustomers

I get the Cannot resolve the collation conflict between “Danish_Norwegian_CI_AS” and “Danish_Greenlandic_100_CI_AS” in the UNION operation error. Any ideas how to resolve this?

Answer

Try this:

select distinct CustomerNo collate Danish_Norwegian_CI_AS from DB1.dbo.TableCustomers
UNION ALL
select distinct CustNo from DB2.dbo.NewTableCustomers