How to create External table with different collation

Question

How to create External table with different collation

Mallikarjuna Avula 1

Please confirm can we create a External table with Different collation on Azure Synapse Database. Can we have different collation on object level ?

Azure Synapse Database collation : Default (SQL_Latin1_General_CP1_CI_AS)
Expected Table creation with Collation : Latin1_General_100_CI_AI_SC_UTF8

Mallikarjuna Avula 1 Reputation point

2022-07-26T07:12:52.71+00:00

Thanks for your reply. We have already tried the above mentioned command, but still it doesn't work and getting below error. Could you please have a look below error and advise.

Invalid collation 'Latin1_General_100_CI_AI_SC_UTF8'.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-07-27T02:23:00.137+00:00

Hello @Mallikarjuna Avula ,
Sorry, the error is misleading. This is due to an access issue. while creating one of my Synapse workspace, somehow I messed up permissions to the Gen2 account and I was able to reproduce the issue on that workspace. I created one more workspace with proper access in place to the gen2 and was able to create table with collation Latin1_General_100_CI_AI_SC_UTF8.

Can you please follow this document and provide 'Storage Blob Contributor' access to your workspace on the gen2?

]3
Mallikarjuna Avula 1 Reputation point

2022-07-28T10:29:04.203+00:00

Hi, Thanks for the detailed info.

We have granted access to the user who trying to create external table, still we are facing the same issue. Please advise.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-07-28T22:06:40.517+00:00

Hello @Mallikarjuna Avula ,

Sorry, This is not related to permissions.

I did further research and find out that when using collation type: 'Latin1_General_100_CI_AI_SC_UTF8' on the dedicated SQL pools, it's throwing the invalid collation error.

But, If I use built-in SQL pools, I can able to create the external table with the same collation.

Could you please confirm, whether you are using Dedicated SQL pools or Build in SQL pools?

Here is the document on the un-supported collation types in Synapse. but I didn't find this collation in that list.

I am checking with my internal team on this. I will get back to you as soon as I hear something on it.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-02T01:00:07.783+00:00

Hello @Mallikarjuna Avula ,
I am just checking in to see if you have any updates here.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-04T20:10:31.407+00:00

Hello @Mallikarjuna Avula ,
I am just checking in to see if you have any updates here.

1 answer

Your answer

Mallikarjuna Avula 1 Reputation point

2022-07-26T07:12:52.71+00:00

Thanks for your reply. We have already tried the above mentioned command, but still it doesn't work and getting below error. Could you please have a look below error and advise.

Invalid collation 'Latin1_General_100_CI_AI_SC_UTF8'.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-07-27T02:23:00.137+00:00

Hello @Mallikarjuna Avula ,
Sorry, the error is misleading. This is due to an access issue. while creating one of my Synapse workspace, somehow I messed up permissions to the Gen2 account and I was able to reproduce the issue on that workspace. I created one more workspace with proper access in place to the gen2 and was able to create table with collation Latin1_General_100_CI_AI_SC_UTF8.

Can you please follow this document and provide 'Storage Blob Contributor' access to your workspace on the gen2?

]3
Mallikarjuna Avula 1 Reputation point

2022-07-28T10:29:04.203+00:00

Hi, Thanks for the detailed info.

We have granted access to the user who trying to create external table, still we are facing the same issue. Please advise.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-07-28T22:06:40.517+00:00

Hello @Mallikarjuna Avula ,

Sorry, This is not related to permissions.

I did further research and find out that when using collation type: 'Latin1_General_100_CI_AI_SC_UTF8' on the dedicated SQL pools, it's throwing the invalid collation error.

But, If I use built-in SQL pools, I can able to create the external table with the same collation.

Could you please confirm, whether you are using Dedicated SQL pools or Build in SQL pools?

Here is the document on the un-supported collation types in Synapse. but I didn't find this collation in that list.

I am checking with my internal team on this. I will get back to you as soon as I hear something on it.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-02T01:00:07.783+00:00

Hello @Mallikarjuna Avula ,
I am just checking in to see if you have any updates here.
Bhargava-MSFT 31,361 Reputation points Microsoft Employee Moderator

2022-08-04T20:10:31.407+00:00

Hello @Mallikarjuna Avula ,
I am just checking in to see if you have any updates here.

Answer 1

Hello @Mallikarjuna Avula ,

Thanks for the question and using MS Q&A platform.

Sorry, you can't create external tables with a different collation on the table level, collation is only applicable on the column level in the external tables.

Please see the below example for your reference.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
[country_code] VARCHAR (5) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[country_name] VARCHAR (100) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
[year] smallint,
[population] bigint
)
WITH (
LOCATION = 'csv/population/population.csv',
DATA_SOURCE = sqlondemanddemo,
FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Also please see this documentation for more details on the external table syntax

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Share via

How to create External table with different collation

1 answer

Your answer