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)?

Do you need to have it Case Sensitive? If not, why not alter the option in MDS to insensitive and manage the case issues in the code? — Fandango68, Jul 25 '20 at 05:40
Do you get the correct behaviour when you use the web UI instead of the excel plugin? — allmhuran, Jul 28 '20 at 14:05
@allmhuran tried changing the text to proper case in the web UI, it doesn't change. — KrazzyNefarious, Aug 04 '20 at 21:54
Apparently, MDS doesn't allow you change collation after entities are setup because of the constraints. — KrazzyNefarious, Aug 19 '20 at 03:09

score 1 · Answer 1 · answered Jul 25 '20 at 05:51

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

I want my database to recognize changes in cases, so I do want it to be case sensitive. My question, can I change my existing case insensitive database to be case sensitive. If yes, how , if no, why. — KrazzyNefarious, Aug 04 '20 at 21:57

Change Collation on Master Data Services

1 Answers1