SQL Server 2019 Server Level collation change to UTF8 invalidating Latin character values

%3CLINGO-SUB%20id%3D%22lingo-sub-757533%22%20slang%3D%22en-US%22%3ESQL%20Server%202019%20Server%20Level%20collation%20change%20to%20UTF8%20invalidating%20Latin%20character%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757533%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20installed%20SQL2019%20CTP3%20in%20our%20Test%20Server%20where%20we%20are%20trying%20to%20convert%20Server%20level%20collation%20from%26nbsp%3BSQL_Latin1_General_CP1_CI_AS%20to%26nbsp%3BLatin1_General_100_CI_AS_SC_UTF8.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20few%20DB's%20available%20on%20the%20server%20and%20we%20were%20using%20following%20command%20to%20change%20the%20server%20level%20collation%3C%2FP%3E%3CP%3Esqlservr%20-c%20-m%20-T4022%20-T3659%20-s%22servername%22%20-q%22Latin1_General_100_CI_AS_SC_UTF8%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20above%20command%20we%20could%20find%20that%20server%20level%20collation%2C%20Database%20level%20collation%20and%20column%20collation%20for%20charter%20types%20are%20getting%20changed.%20Few%20of%20our%20tables%20stores%20Latin%20characters%20in%20column%20values.%20with%20the%20above%20change%20we%20could%20find%20the%20Latin%20characters%20are%20getting%20updated%20with%20some%20invalid%20characters%20which%20is%20causing%20our%20application%20functionality%20failure%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EValues%20before%20Server%20level%20collation%20change%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%E2%82%AC%E2%80%9A%C6%92%E2%80%9E%E2%80%A6%E2%80%A0%E2%80%A1%CB%86%E2%80%B0%C5%A0%E2%80%B9%C5%92%C5%BD%E2%80%98%E2%80%99%E2%80%9C%E2%80%9D%E2%80%A2%E2%80%93%E2%80%94%CB%9C%E2%84%A2%C5%A1%E2%80%BA%C5%93%C5%BE%C5%B8%C2%A1%C2%A2%C2%A3%C2%A4%C2%A5%C2%A6%C2%A7%C2%A8%C2%A9%C2%AA%C2%AB%C2%AC-%C2%AE%C2%AF%C2%B0%C2%B1%C2%B2%C2%B3%C2%B4%C2%B5%C2%B6%E2%80%A2%C2%B8%C2%B9%C2%BA%C2%BB%C2%BC%C2%BD%C2%BE%C2%BF%C3%80%C3%81%C3%82%C3%83%C3%84%C3%85%C3%86%C3%87%C3%88%C3%89%C3%8A%C3%8B%C3%8C%C3%8D%C3%8E%C3%8F%C3%90%C3%91%C3%92%C3%93%C3%94%C3%95%C3%96%C3%97%C3%98%C3%99%C3%9A%C3%9B%C3%9C%C3%9D%C3%9E%C3%9F%C3%A0%C3%A1%C3%A2%C3%A3%C3%A4%C3%A5%C3%A6%C3%A7%C3%A8%C3%A9%C3%AA%C3%AB%C3%AC%C3%AD%C3%AE%C3%AF%C3%B0%C3%B1%C3%B2%C3%B3%C3%B4%C3%B5%C3%B6%C3%B7%C3%B8%C3%B9%C3%BA%C3%BB%C3%BC%C3%BD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EValues%20after%20collation%20change%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD-%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20has%20faced%20this%20issue%3F%20do%20we%20have%20any%20resolution%20to%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1010283%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%202019%20Server%20Level%20collation%20change%20to%20UTF8%20invalidating%20Latin%20character%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1010283%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20this%20still%20happening%20in%20RTM%3F%20If%20you%20tested%20and%20it%20is%2C%20please%20reach%20out%20to%20customer%20support%2C%20or%20email%20me%20at%20pedro%20dot%20lopes%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41501%22%20target%3D%22_blank%22%3E%40microsoft%3C%2FA%3E.com%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1010968%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%202019%20Server%20Level%20collation%20change%20to%20UTF8%20invalidating%20Latin%20character%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1010968%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F194232%22%20target%3D%22_blank%22%3E%40Pedro%20Lopes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply%2C%20we%20will%20test%20with%20RTM%20version%20and%20will%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ELata%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019944%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%202019%20Server%20Level%20collation%20change%20to%20UTF8%20invalidating%20Latin%20character%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019944%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377191%22%20target%3D%22_blank%22%3E%40Latav_Tech%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHi%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F194232%22%20target%3D%22_blank%22%3E%40Pedro%20Lopes%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20performed%20testing%20with%20SQL%20server%202019%20RTM%20version%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%3CEM%3E%22Microsoft%20SQL%20Server%202019%20(RTM-GDR)%20(KB4517790)%20-%2015.0.2070.41%20(X64)%26nbsp%3B%26nbsp%3B%20Oct%2028%202019%2019%3A56%3A59%26nbsp%3B%26nbsp%3B%20Copyright%20(C)%202019%20Microsoft%20Corporation%26nbsp%3B%20Developer%20Edition%20(64-bit)%20on%20Windows%20Server%202016%20Datacenter%2010.0%20%3CX64%3E%20(Build%2014393%3A%20)%20(Hypervisor)%22%20%3C%2FX64%3E%3C%2FEM%3E%3C%2FFONT%3Eand%20still%20seeing%26nbsp%3Bthis%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActual%20data%20before%20conversion%20is%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%22%E2%82%AC%E2%80%9A%C6%92%E2%80%9E%E2%80%A6%E2%80%A0%E2%80%A1%CB%86%E2%80%B0%C5%A0%E2%80%B9%C5%92%C5%BD%E2%80%98%E2%80%99%E2%80%9C%E2%80%9D%E2%80%A2%E2%80%93%E2%80%94%CB%9C%E2%84%A2%C5%A1%E2%80%BA%C5%93%C5%BE%C5%B8%C2%A1%C2%A2%C2%A3%C2%A4%C2%A5%C2%A6%C2%A7%C2%A8%C2%A9%C2%AA%C2%AB%C2%AC-%C2%AE%C2%AF%C2%B0%C2%B1%C2%B2%C2%B3%C2%B4%C2%B5%C2%B6%E2%80%A2%C2%B8%C2%B9%C2%BA%C2%BB%C2%BC%C2%BD%C2%BE%C2%BF%C3%80%C3%81%C3%82%C3%83%C3%84%C3%85%C3%86%C3%87%C3%88%C3%89%C3%8A%C3%8B%C3%8C%C3%8D%C3%8E%C3%8F%C3%90%C3%91%C3%92%C3%93%C3%94%C3%95%C3%96%C3%97%C3%98%C3%99%C3%9A%C3%9B%C3%9C%C3%9D%C3%9E%C3%9F%C3%A0%C3%A1%C3%A2%C3%A3%C3%A4%C3%A5%C3%A6%C3%A7%C3%A8%C3%A9%C3%AA%C3%AB%C3%AC%C3%AD%C3%AE%C3%AF%C3%B0%C3%B1%C3%B2%C3%B3%C3%B4%C3%B5%C3%B6%C3%B7%C3%B8%C3%B9%C3%BA%C3%BB%C3%BC%C3%BD%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20after%20conversion%20is%20%3A%3C%2FP%3E%3CP%3E%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD-%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%EF%BF%BD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20using%20following%20command%20to%20perform%20the%20conversion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esqlservr%20-c%20-m%20-T4022%20-T3659%20-s%22RTMUTF8%22%20-q%22Latin1_General_100_CI_AS_SC_UTF8%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ELata%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

We have installed SQL2019 CTP3 in our Test Server where we are trying to convert Server level collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_100_CI_AS_SC_UTF8.

 

We have few DB's available on the server and we were using following command to change the server level collation

sqlservr -c -m -T4022 -T3659 -s"servername" -q"Latin1_General_100_CI_AS_SC_UTF8"

 

With the above command we could find that server level collation, Database level collation and column collation for charter types are getting changed. Few of our tables stores Latin characters in column values. with the above change we could find the Latin characters are getting updated with some invalid characters which is causing our application functionality failure

 

Values before Server level collation change :

 

€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ¡¢£¤¥¦§¨©ª«¬-®¯°±²³´µ¶•¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüý

 

Values after collation change

 

���������������������������������������-��������������������������������������������������������������������������������

 

Does anyone has faced this issue? do we have any resolution to this issue?

3 Replies
Highlighted

Is this still happening in RTM? If you tested and it is, please reach out to customer support, or email me at pedro dot lopes @microsoft.com

Highlighted

Hi @Pedro Lopes 

 

Thanks for the reply, we will test with RTM version and will update.

 

Thanks

Lata

Highlighted

@Latav_Tech 

 

Hi @Pedro Lopes 

 

We have performed testing with SQL server 2019 RTM version 

"Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)   Oct 28 2019 19:56:59   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)" and still seeing this issue.

 

Actual data before conversion is : 

"€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ¡¢£¤¥¦§¨©ª«¬-®¯°±²³´µ¶•¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüý"

 

Data after conversion is :

���������������������������������������-��������������������������������������������������������������������������������

 

We are using following command to perform the conversion.

 

sqlservr -c -m -T4022 -T3659 -s"RTMUTF8" -q"Latin1_General_100_CI_AS_SC_UTF8"

 

Thanks

Lata