UCMDB Support Tip: Database Collation on SQL Server
When UCMDB is configured to use SQL Server default database settings, collation of the CMDB database must be the same as the collation of Tempdb database (storage for temp tables).
If these collations are not the same, TQL calculations that involve temp tables and string comparison will fail with a database error, e.g. "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation".
Collation of the temp tables is equal to the collation of the database server itself, which is set upon db server setup.
How to check collation settings
To check the collations in your SQL Server database server, run the following script. It will list collations for every database on the db server, so you can see whether they are the same and correspond to the collation of the db server itself.
SELECT substring(CAST(SERVERPROPERTY( 'Collation' ) as varchar), 1, 50) AS Server_Default_Collation;
print '>>> select * from master.sys.databases where collation_name<>SERVERPROPERTY( ''Collation'' )'
select substring(name, 1, 50) as "DB_NAME", database_id,create_date, compatibility_level, collation_name from master.sys.databases where collation_name<>SERVERPROPERTY( 'Collation' )
print '>>> columns having non-default collation'
exec sp_msforeachdb 'use ?
if (db_name() not like ''ReportServer%'')
print db_name() + '' having collation: '' + CAST(DATABASEPROPERTYEX( ''?'' , ''Collation'' ) as varchar)
select substring(T.name, 1, 30) "tbl_name", T.object_id, substring(C.name,1,30) "col_name", C.column_id, C.system_type_id, C.user_type_id, C.max_length, C.collation_name
from ?.sys.columns C inner join ?.sys.tables T on (C.object_id=T.object_id)
where C.collation_name<>CAST(SERVERPROPERTY( ''Collation'' ) as varchar)
If you find this or any post resolves your issue, please be sure to mark it as an accepted solution."
Click the KUDOS star on the left to say 'Thanks'