search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.28k stars 21.47k forks source link

Lack of information #103072

Closed NTurakulov closed 5 months ago

NTurakulov commented 1 year ago

Documentation is useless in the way it is!

Root issue:

My Power BI reports represent Cyrillic characters as ?????? ???-???? and Google says I need to change collation of data base. According to documentation it's pretty simple:

To change the default collation for dedicated SQL pool database, update to the Collation field in the provisioning experience.

But what is provisioning experience? Any link, description or a screenshot? Where should I look for it? Azure Portal doesn't have such option or mention at all. Azure Synapse Analytics portal it read only image

Ok I'm happy there is an another option:

To change the default collation for a serverless SQL pool database, you can use ALTER DATABASE statement.

Try it in Management Studio or Synapse portal:

ALTER DATABASE mydb COLLATE Latin1_General_100_BIN2_UTF8;

and get:

The database could not be exclusively locked to perform the operation

To fix that it's recommended to:

ALTER DATABASE carlar SET SINGLE_USER WITH ROLLBACK IMMEDIATE

which is

SINGLE_USER is not supported for ALTER DATABASE

At the end

How should I change collation for my Azure Synapse Analytics database to UTF-8? Please update the page with actual istructions

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Naveenommi-MSFT commented 1 year ago

@NTurakulov Thanks for your feedback! We will investigate and update as appropriate.

Naveenommi-MSFT commented 1 year ago

@NTurakulov I've delegated this to content author @filippopovic, who will review it and offer his insightful opinions.

filippopovic commented 1 year ago

@NTurakulov, is this related to dedicated or serverless SQL pool?

iamdruid commented 1 year ago

@filippopovic, I am experiencing the same blockers as @NTurakulov on a serverless SQL pool. Is there some guidance you could provide on how to change the collation of a serverless pool? This creates a significant challenge in dynamic file parsing by the serverless SQL engine. I am already prepared for the fact that views and external tables will need to be recreated/altered after the collation is changed. The ALTER DATABASE... statement appears that it should work, but this is blocked by requiring exclusive lock on the database. Is it worth going down the unclear path of killing sessions or is this not the right track?

filippopovic commented 1 year ago

@iamdruid sorry for delayed reponse. KILL-ing user sessions is fine. Another thing you can do is to specify collations at the column level. Either in CREATE EXTERNAL TABLE, or in OPENROWSET using WITH clause. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

bandersmsft commented 5 months ago

Thanks for your dedication to our documentation. Unfortunately, at this time we have been unable to review your issue in a timely manner and we sincerely apologize for the delayed response. We are closing this issue for now, but if you feel that it's still a concern, please respond and let us know. If you determine another possible update to our documentation, please don't hesitate to reach out again. #please-close