Cannot resolve collation conflict in Union select

Question

Hi,

I've got tqo 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

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.