Error while creating external table in data lake synapse

Question

Error while creating external table in data lake synapse

V Mishra, Devendranath 1

Operation CREATE EXTERNAL FILE FORMAT is not allowed for a replicated database.

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-08-31T18:47:05.187+00:00
Hello @V Mishra, Devendranath ,

Thanks for the question and using MS Q&A platform.
Could you please share few additional details on

How you are trying to create the external table - are you using UI option (right click on file and select Create External Table option) or are you using query to create the external table?

Is it Serverless or Dedicated SQL pool?

Is your data lake the default ADLS associated with your Synapse workspace or is it something exported form Dataverse using Synapse link?

Also I would recommend you to please refer to this self help doc and see if your scenario is related to this: Operation isn't allowed for a replicated database

Also please refer to this documentation for related pre-requisites information about necessary permission and related syntaxes

CREATE EXTERNAL TABLE (Transact-SQL)

CREATE EXTERNAL FILE FORMAT (Transact-SQL)

Let us know if this helps.

1 answer

Your answer

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-08-31T18:47:05.187+00:00

Hello @V Mishra, Devendranath ,

Thanks for the question and using MS Q&A platform.
Could you please share few additional details on

How you are trying to create the external table - are you using UI option (right click on file and select Create External Table option) or are you using query to create the external table?

Is it Serverless or Dedicated SQL pool?

Is your data lake the default ADLS associated with your Synapse workspace or is it something exported form Dataverse using Synapse link?

Also I would recommend you to please refer to this self help doc and see if your scenario is related to this: Operation isn't allowed for a replicated database

Also please refer to this documentation for related pre-requisites information about necessary permission and related syntaxes

CREATE EXTERNAL TABLE (Transact-SQL)

CREATE EXTERNAL FILE FORMAT (Transact-SQL)

Let us know if this helps.

Answer 1

KranthiPakala-MSFT 46,737 Microsoft Employee Moderator

Hello @V Mishra, Devendranath ,

By chance are you using/pointing to a lake database instead of Serverless/Dedicated SQL database while creating the External table. I was testing the same and if you use a lake database for external table then it results in the same error which you are seeing. Please see below image.

When I changed back the connection to a Dedicated SQL pool, it worked as expected without any errors.

And the external table was created as expected.

Hope this info helps

------------------------------

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

KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator

2022-09-05T23:59:42.9+00:00

Hello @V Mishra, Devendranath ,

Just checking in to see if the above answer helped. If it answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Share via

Error while creating external table in data lake synapse

1 answer

Your answer