Operation CREATE EXTERNAL FILE FORMAT is not allowed for a replicated database on Azure Synapse SQL Built-in Serverless Pool

Question

I am trying to Create and query external tables from a file in Azure Data Lake from Azure Synapse Serverless SQL Pool using the following guide:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

Everything appears to be going fine. I have created the following script to create an external table:

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 
    WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
           FORMAT_OPTIONS (
             FIELD_TERMINATOR = ',',
             USE_TYPE_DEFAULT = FALSE
            ))
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'synapsefilename_synapselakev2_dfs_core_windows_net') 
    CREATE EXTERNAL DATA SOURCE [synapsefilename_synapselakev2_dfs_core_windows_net] 
    WITH (
        LOCATION = 'abfss://synapsefilename@synapselakev2.dfs.core.windows.net' 
    )
GO

CREATE EXTERNAL TABLE GlobalOptionsetMetadata (
    [C1] nvarchar(4000),
    [C2] nvarchar(4000),
    [C3] nvarchar(4000),
    [C4] nvarchar(4000),
    [C5] nvarchar(4000),
    [C6] nvarchar(4000),
    [C7] nvarchar(4000)
    )
    WITH (
    LOCATION = 'GlobalOptionsetMetadata.csv',
    DATA_SOURCE = [synapsefilename_synapselakev2_dfs_core_windows_net],
    FILE_FORMAT = [SynapseDelimitedTextFormat]
    )
GO


SELECT TOP 100 * FROM dbo.GlobalOptionsetMetadata
GO

However, when I click run I get the following error:

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

Any thoughts?

Answer 1

it is most likely Database1 is a "replicated" lake database which you can't create external file formats. you have to select a Serverless/Dedicated SQL database while creating the External table. Try to change the database in "Use Database" combobox near to "Connect to" at top of query window and select Serverless/Dedicated SQL Database instead.