Highlighted
Absent Member.. Absent Member..
Absent Member..
132 views

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%'')

      begin

            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)

      end'

 
"HP Support
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'
Labels (1)
Tags (1)
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.