Cannot resolve collation conflict in Union select

Question

I've got two queries:

First doesn't work:

select hotels.TargetCode as TargetCode from hotels
union all 
select DuplicatedObjects.duplicatetargetCode as TargetCode 
from DuplicatedObjects where DuplicatedObjects.objectType=4

because I get error:

Cannot resolve collation conflict for column 1 in SELECT statement.

Second works:

select hotels.Code from hotels where hotels.targetcode is not null 
union all 
select DuplicatedObjects.duplicatetargetCode as Code 
from DuplicatedObjects where DuplicatedObjects.objectType=4 

Structure:

Hotels.Code -PK nvarchar(40)
Hotels.TargetCode - nvarchar(100)

DuplicatedObjects.duplicatetargetCode PK nvarchar(100)

Answer 1

Trying to set collation in a query when joining a linked server can still fail with Incorrect syntax near 'COLLATE' even though your syntax is correct.

Solution: In Linked Server Properties, set Use Remote Collation to False, and enter the desired collation type in Collation Name - removes need to force collation in your query.

Answer 2

You need to add the collation statement in the select part as well - not only in the where clause - like the following:

select a.field1 collate DATABASE_DEFAULT, b.otherfield from table1 a, table2 b 
where a.field1 collate DATABASE_DEFAULT = b.field3

Answer 3

Use sp_help on both tables. The collation on hotels.TargetCode is different from the collation on DuplicatedObjects.duplicateTargetCode, so the DB doesn't know what to do with the resulting UNION.

You can force a new collation on one of them to match the other, or put the results into a predefined temp table/table which will have a collation defined already.

EDIT: You can override the existing collation using something like...

DuplicatedObjects.duplicateTargetCode COLLATE SQL_Latin1_General_CP1_CI_AS

...in the query. This will use the duplicateTargetCode with the collation SQL_Latin1_General_CP1_CI_AS. You should choose a collation which matches that of hotels.TargetCode.