Cannot resolve collation conflict in Union select

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:

Cann开发者_Go百科ot 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)

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

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.

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.

Your collation conflict may go away if you declare the temp table #list as

 CREATE TABLE #list
 (
 record_num INT IDENTITY(1,1),
 TempAcctNum NVARCHAR(40) Collate Database_Default,
 TempAcctName NVARCHAR(100) Collate Database_Default,
 TempNumOfCrds SMALLINT,
 TempSys2Acct NVARCHAR(10) Collate Database_Default,
 TempDelType TINYINT,
 TempStatusOfCrd VARCHAR(100) Collate Database_Default,
 TempLastDate VARCHAR(100) Collate Database_Default,
 TempSys2Acct1 NVARCHAR(10) Collate Database_Default,
 TempShrtName NVARCHAR(50) Collate Database_Default,
 TempAdd1 NVARCHAR(200) Collate Database_Default,
 TempAdd2 NVARCHAR(200) Collate Database_Default,
 TempCity NVARCHAR(100) Collate Database_Default,
 TempState NVARCHAR(100) Collate Database_Default,
 TempZipCode NVARCHAR(50) Collate Database_Default,
 TempOpenDate DATETIME,
 TempFax NVARCHAR(50) Collate Database_Default,
 TempUsr1 NVARCHAR(100) Collate Database_Default,
 TempUsr2 NVARCHAR(100) Collate Database_Default,
 TempUsr3 NVARCHAR(100) Collate Database_Default,
 TempUsr4 NVARCHAR(100) Collate Database_Default,
 TempMemo NTEXT,
 TempMail NVARCHAR(100) Collate Database_Default,
 TempNoSys2Status NVARCHAR(50) Collate Database_Default,
 TempDelete BIT,
 TempEdit BIT,
 TempContName VARCHAR(200) Collate Database_Default,
 TempPhone NVARCHAR(50) Collate Database_Default
 )