Change Collation on Master Data Services

Question

I have a SQL Server 2017 Master Data Services installed on SQL Server 2017. During installation it was setup to use the default server collation. We now have been using MDS via the excel plugin and the users wish to clean the text. However, when setting the text to proper cases, the publish action returns as if the data was unchanged. The collation on the MDS database is Latin1_General_CI_AS.

My questions is what is the best way to enable case sensitivity on MDS? Is it just changing the collation (say, to Latin1_General_CS_AS)?

Answer 1

When you've setup the MDS configuations, you would have perhaps ticked Case Sensitive.

Just go back in there and fix it.

To me, that is the best way. Make sure though that all objects in the database are also of the same collation:

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

If the database has restrictions from the DBA, or you don't have all the priveleges, you may have no choice, but to specify the collation in the code yourself:

SELECT * FROM tbl where x = 'y' COLLATE sql_latin1_general_cp1_cs_as