SQL Server 2008 Collation conflict - how to resolve?

Question

For simplification, POC, I have the following query, using character typed columns:

select AH_NAME1 from GGIMAIN.SYSADM.BW_AUFTR_KOPF
union
select AH_NAME1 from GGI2014.SYSADM.BW_AUFTR_KOPF

and I get the following error:

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation.

GGI2014 was indeed created with collation SQL_Latin1_General_CP1_CI_AS. This has been changed in SMS and the instance has been restarted, also in SMS.

When I look in SMS, as well as query:

select name, collation_name from sys.databases

all indications are that both GGIMAIN and GGI2014 are collated Latin1_General_CS_AS.

Does anyone have any advice on what else needs to be done?

Thanks,

Matt

Answer 1

I add a collate for each field of the query

SELECT Field1 collate default_database
,field2 collate default_database
,fieldn collate default_database
From DB1.dbo.table_x
UNION ALL
SELECT Field1 collate default_database
,field2 collate default_database
,fieldn collate default_database
From DB2.dbo.table_y

PD.only in the query that gives the error

I hope it works and I got them out of trouble

Answer 2

select AH_NAME1 COLLATE DATABASE_DEFAULT from GGIMAIN.SYSADM.BW_AUFTR_KOPF
union
select AH_NAME1 COLLATE DATABASE_DEFAULT from GGI2014.SYSADM.BW_AUFTR_KOPF

Unless I am mistaken, changing the collation of the database does not change the collation of the already existing objects. Only new objects will be affected

Answer 3

Try this one (maybe you're columns have different collation) -

SELECT AH_NAME1 COLLATE database_default
FROM GGIMAIN.SYSADM.BW_AUFTR_KOPF

UNION

SELECT AH_NAME1 COLLATE database_default
FROM GGI2014.SYSADM.BW_AUFTR_KOPF