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

Accepted Answer

```
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

SQL Server 2008 Collation conflict - how to resolve?

Solution

Problem