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
Collation conflicts Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the like operation. This may prompt an issue when programming for SQL Server. Users can resolve this issue by utilizing a SQL Server column compare clause with COLLATE as described above.
In Microsoft SQL Server, the collation can be set at the column level. When you compare (or concatenate) two columns having different collation in a query, this error occurs: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation. —
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation. Msg 5072, Level 16, State 1, Line 2 ALTER DATABASE failed. The default collation of database 'land_gis' cannot be set to Persian_100_CI_AS.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With