Error : External table 'habitant_gold' is not accessible because location does not exist or it is used by another process.

Question

Error : External table 'habitant_gold' is not accessible because location does not exist or it is used by another process.

Mehdi SAADI 0

When I run my code :

CREATE DATABASE datalake_gold_db;

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Welcome@MS92';

CREATE DATABASE SCOPED CREDENTIAL MyCred

WITH IDENTITY='SHARED ACCESS SIGNATURE',

SECRET='SAS Token';

CREATE EXTERNAL DATA SOURCE silver_datalake

WITH (

LOCATION = 'abfss://******@talendazurestoragee.dfs.core.windows.net/silver',

CREDENTIAL=MyCred

);

-- Étape 2 : Créer le FILE FORMAT CSV (à faire une seule fois)

CREATE EXTERNAL FILE FORMAT csv_format

WITH (

FORMAT_TYPE = DELIMITEDTEXT,

FORMAT_OPTIONS (

FIELD_TERMINATOR = ',',

STRING_DELIMITER = '"',

FIRST_ROW = 2 -- car première ligne = en-tête

)

);

-- Création de la table externe traffic_gold

CREATE EXTERNAL TABLE traffic_gold (

frc VARCHAR(255),

currentSpeed INT,

freeFlowSpeed INT,

currentTravelTime INT,

freeFlowTravelTime INT,

confidence FLOAT,

roadClosure VARCHAR(255),

ville VARCHAR(255),

timestamp DATETIME,

congestionLevel INT,

isCongested VARCHAR(255)

)

WITH (

LOCATION = '/traffic/',

DATA_SOURCE = silver_datalake,

FILE_FORMAT = csv_format

);

-- Création de la table externe meteo_gold

CREATE EXTERNAL TABLE meteo_gold (

ville VARCHAR(255),

temperature FLOAT,

temperature_ressentie FLOAT,

temp_min FLOAT,

temp_max FLOAT,

pression INT,

humidite INT,

visibilite INT,

lat FLOAT,

lon FLOAT,

vent_vitesse FLOAT,

vent_direction INT,

condition VARCHAR(255),

description VARCHAR(255),

date DATETIME

)

WITH (

LOCATION = '/meteo/',

DATA_SOURCE = silver_datalake,

FILE_FORMAT = csv_format

);

-- Création de la table externe habitant_gold

CREATE EXTERNAL TABLE habitant_gold (

ville VARCHAR(255),

nombre_habitants INT,

type_agglomeration VARCHAR(255)

)

WITH (

LOCATION = '/data-nbhabitants-france/CitiesofFrance-VillesdeFrance.csv',

DATA_SOURCE = silver_datalake,

FILE_FORMAT = csv_format

);

-- Création de la table externe demographie_gold

CREATE EXTERNAL TABLE demographie_gold (

ville VARCHAR(255),

chomage FLOAT,

chomage_jeunes FLOAT,

chomage_long FLOAT,

evolu_emploi_cree FLOAT,

activite FLOAT,

emploi_feminin FLOAT,

emploi_commune FLOAT,

defaill_entreprise FLOAT,

salaire_annuel INT,

criminalite FLOAT,

evolution_crimes FLOAT,

securite_routiere FLOAT,

inondations INT,

terrains_pollues INT,

usines_risques INT,

mortalite_infantile FLOAT,

mortalite_cancer_poumon FLOAT,

mortalite_alcool FLOAT,

deces_infarctus FLOAT,

taux_suicide FLOAT,

mortalite_globale FLOAT,

taille_classes_primaires FLOAT,

retard_6eme FLOAT,

retard_3eme FLOAT,

retard_terminale FLOAT,

metre_carre_ancien INT,

taxe_habitation FLOAT,

foncier_bati FLOAT,

metre_cube_eau FLOAT,

proprietaires FLOAT,

logt_sup_4pieces FLOAT,

logt_insalubre FLOAT,

logt_vacant FLOAT,

logt_construction FLOAT,

mer INT,

ski INT,

soleil INT,

pluie FLOAT,

temperature FLOAT,

marche_a_pied FLOAT,

musees INT,

cinema FLOAT,

monum_historiques FLOAT,

pret_livres FLOAT,

restaur_distingues FLOAT,

presse FLOAT,

etudiants INT,

impot_revenu FLOAT,

impot_fortune FLOAT,

imposables FLOAT,

evol_demographique FLOAT,

vieillissement FLOAT,

attirance_globale FLOAT,

attirance_actifs FLOAT

)

WITH (

LOCATION = '/data-demographie-france/French_cities_2005.csv',

DATA_SOURCE = silver_datalake,

FILE_FORMAT = csv_format

);

I have the following error :

SELECT * FROM habitant_gold;
External table 'habitant_gold' is not accessible because location does not exist or it is used by another process.

I also grant the managed identity the "Storage Blob Data Contributor" role on the container that contains the external table, in the attached screen. I have a student account...

But I still have the same error... Can someone help me, please ?

Mehdi SAADI 0 Reputation points

2025-04-13T18:12:30.2566667+00:00

The attached screen
Ganesh Gurram 6,125 Reputation points Microsoft External Staff

2025-04-14T04:24:08.1666667+00:00
@Mehdi SAADI

The error message you're encountering—"External table 'habitant_gold' is not accessible because location does not exist or it is used by another process"—typically arises due to issues related to file path accuracy, permissions, or file availability.

Based on Microsoft's documentation and community insights, here are the steps you can take to resolve this issue:

Verify the File Path and Case Sensitivity - Azure Data Lake Storage Gen2 paths are case-sensitive. Ensure that the file path specified in your external table definition matches the actual path and filename, including the correct casing. For example: LOCATION = '/data-nbhabitants-france/CitiesofFrance-VillesdeFrance.csv'If the actual file is named citiesofFrance-VillesdeFrance.csv, this mismatch will cause the error.

Check Permissions - Even if you've assigned the "Storage Blob Data Contributor" role to your Synapse workspace's managed identity, ensure that:

The role is assigned at the correct scope—ideally at the container level (datalake) or the specific folder level.

The permissions have propagated, which can sometimes take a few minutes.

This problem usually happens because your Azure Active Directory identity doesn't have rights to access the file.

File Availability - Make sure that the file CitiesofFrance-VillesdeFrance.csv is indeed present in the specified location (/data-nbhabitants-france/) in your ADLS container.

Ensure No Concurrent Access Issues - The error message also mentions that the file might be "used by another process." Ensure that no other processes or applications are currently writing to or locking the file, as this can prevent read access.

For more details refer:

Create and use native external tables using SQL pools in Azure Synapse Analytics

Access external storage using serverless SQL pool in Azure Synapse Analytics

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Mehdi SAADI 0 Reputation points

2025-04-14T17:38:56.82+00:00

Hello Ganesh,

Thank you for your reply.

I don't use Azure Data lake Storage Gen2 for my storage but rather Azure Blob Storage, as shown in the screenshot.

I followed your instructions and made the requested checks, but the error persists.

I don't know why...
Ganesh Gurram 6,125 Reputation points Microsoft External Staff

2025-04-15T05:59:44.73+00:00
@Mehdi SAADI

Thanks for the update and the screenshot. Based on the latest image and your reply, I see that:

You're using Azure Blob Storage, not ADLS Gen2, though it seems like the storage account is hierarchical namespace-enabled (i.e., compatible with abfss protocol).

The file you want to access is: French_cities_2005.csv And it's stored at: datalake/silver/data-demographie-france/French_cities_2005.csv

But in your CREATE EXTERNAL TABLE statement for habitant_gold, you're using: LOCATION = '/data-nbhabitants-france/CitiesofFrance-VillesdeFrance.csv'

This path does not match what is shown in the storage explorer. The file CitiesofFrance-VillesdeFrance.csv doesn't exist at that path.

Corrected habitant_gold Table (based on your storage structure):

CREATE EXTERNAL TABLE habitant_gold ( city VARCHAR(255), temperature FLOAT, temperature_ressentie FLOAT, temp_min FLOAT, temp_max FLOAT, pressure INT, humidity INT, visibility INT, lat FLOAT, lon FLOAT, vent_vitesse FLOAT, vent_direction INT, condition VARCHAR(255), description VARCHAR(255), date DATETIME ) WITH ( LOCATION = 'silver/data-demographie-france/French_cities_2005.csv', DATA_SOURCE = silver_datalake, FILE_FORMAT = csv_format );

Or if this file is meant for demographie_gold, and CitiesofFrance-VillesdeFrance.csv is missing, then you should remove or fix the habitant_gold table reference until that file is actually uploaded.

Path should not start with a forward slash / – This is important in serverless SQL pool when specifying LOCATION in external table definitions.

I hope this information helps!
Mehdi SAADI 0 Reputation points

2025-04-15T18:17:01.35+00:00

Hello Ganesh,

Thank you again for your message.

I followed your instructions, but I still have the same error. I tried to remove the ‘Credential’ part, since I'm using ‘Managed Identity’ with Storage Blot Contributor. Here are some screenshots of my various SQL scripts and IAM accesses.
Mehdi SAADI 0 Reputation points

2025-04-15T18:19:53.6133333+00:00

Hello Ganesh,

Thank you again for your message.

I followed your instructions, but I still have the same error. I tried to remove the ‘Credential’ part, since I'm using ‘Managed Identity’ with Storage Blot Contributor. Here are some screenshots of my various SQL scripts and IAM accesses.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-16T15:28:41.5566667+00:00
Hello @@Mehdi SAADI,

You can follow the below steps to create an external table from the synapse workspace:

Navigate to you data section and to your ADLS gen2 and pointing to the file like shown in the below image:

Next, you will have a window for the new external table:

After you click on continue here you will get an option to select the serverless db and your table name

Once you click on the open script you will be able to query the data from the file.

That includes for example

SELECT TOP 100 * FROM dbo.dilipext

Also as you mentioned that you are using the Manged identity

Make sure you also provide the Storage blob contributor role to you Manged Identity like below:
Mehdi SAADI 0 Reputation points

2025-04-16T17:46:16.52+00:00

Hello @Dileep Raj Narayan Thumula

Thank you for your message.

The problem is that I don't use ADLS gen2 but Azure Blob Storage. So on ADLS gen2, my database is empty.

The problem is that I'd like to connect it to my datalake database on my Azure Blob Storage.

Dileep Raj Narayan Thumula 0 Microsoft External Staff

Hi @Mehdi SAADI,

Thank you for the reply and confirming I have tried some approach which might help you.

Hi

As you mentioned that you are using the Azure blob storage.

I have tried the same at my end below is the srcipt for my sample external table:

CREATE  DATABASE Serverlessdb02

Use serverlessdb02

CREATE  MASTER  KEY  ENCRYPTION  BY  PASSWORD = 'Diliprajnarayan@2'

CREATE  DATABASE  SCOPED  CREDENTIAL credentialobject20

WITH  IDENTITY ='SHARED ACCESS SIGNATURE',

SECRET ='<**YOUR SAS TOKEN**>'

GO;

CREATE  EXTERNAL  DATA  SOURCE demoDatasource

WITH( LOCATION = 'https://blbaz02.blob.core.windows.net/'

, CREDENTIAL = credentialobject20

);

CREATE  EXTERNAL  FILE  FORMAT F1_fileformat_csv

WITH (

FORMAT_TYPE = DELIMITEDTEXT,

FORMAT_OPTIONS (

FIELD_TERMINATOR = ',',

STRING_DELIMITER = '"',

FIRST_ROW = 2,

USE_TYPE_DEFAULT = TRUE

)

);

CREATE  EXTERNAL  TABLE dilipext12 (

[Player Name] varchar(50),

[Matches] varchar(500),

[Runs] INT,

[Average] DECIMAL(5,2)

)

WITH(

LOCATION ='/dilipf1/Australian_batsmen (1).csv',

DATA_SOURCE = demoDatasource,

FILE_FORMAT = F1_fileformat_csv

)

GO

I am thinking the ERROR is because of the EXTERNAL DATA SOURCE because in your external table you are using the below

enter image description here

But you need to use something like this

'https://blbaz02.blob.core.windows.net/'

Also please note that I am also having storage blob contributor role

Results:

select * from dilipext12

enter image description here

Mehdi SAADI 0 Reputation points

2025-04-17T08:57:15.22+00:00

Hello @Dileep Raj Narayan Thumula

Thank you for your message.

I am using an abfs link as I need to specify the Azure storage account and container. Otherwise Synapse can know where to create the data?

in my link:

'abfss://******@talendazurestoragee.dfs.core.windows.net/silver'

datalake corresponds to my container and talendazurestoragee is my azure storage account.

Also, I don't use a credential because I've added a Managed Identity to my Synapse syn11 space with an Azure Storage Contributor role in the IAM Access Control tab.

Where do you think the problem lies?
Mehdi SAADI 0 Reputation points

2025-04-17T09:52:18.5966667+00:00

I use a new query :

But I always come across the same mistake with this data source :

With the same csv format :

And here is the table I created :

But I keep getting the same error, even though I'm using Managed Identity with my synapse syn11 space in Azure Blob Storage Contributor...
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-21T11:38:32.29+00:00

Hello @Mehdi SAADI,

Could you please check and share me the image if you have the storage blob contributor access to your managed identity.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-22T11:29:58.76+00:00

Hello @ Mehdi SAADI,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-23T12:21:37.24+00:00

Hello @ Mehdi SAADI,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Mehdi SAADI 0 Reputation points

2025-04-13T18:12:30.2566667+00:00

The attached screen
Mehdi SAADI 0 Reputation points

2025-04-14T17:38:56.82+00:00

Hello Ganesh,

Thank you for your reply.

I don't use Azure Data lake Storage Gen2 for my storage but rather Azure Blob Storage, as shown in the screenshot.

I followed your instructions and made the requested checks, but the error persists.

I don't know why...
Ganesh Gurram 6,125 Reputation points Microsoft External Staff

2025-04-15T05:59:44.73+00:00

@Mehdi SAADI

Thanks for the update and the screenshot. Based on the latest image and your reply, I see that:

You're using Azure Blob Storage, not ADLS Gen2, though it seems like the storage account is hierarchical namespace-enabled (i.e., compatible with abfss protocol).

The file you want to access is: French_cities_2005.csv And it's stored at: datalake/silver/data-demographie-france/French_cities_2005.csv

But in your CREATE EXTERNAL TABLE statement for habitant_gold, you're using: LOCATION = '/data-nbhabitants-france/CitiesofFrance-VillesdeFrance.csv'

This path does not match what is shown in the storage explorer. The file CitiesofFrance-VillesdeFrance.csv doesn't exist at that path.

Corrected habitant_gold Table (based on your storage structure):

CREATE EXTERNAL TABLE habitant_gold ( city VARCHAR(255), temperature FLOAT, temperature_ressentie FLOAT, temp_min FLOAT, temp_max FLOAT, pressure INT, humidity INT, visibility INT, lat FLOAT, lon FLOAT, vent_vitesse FLOAT, vent_direction INT, condition VARCHAR(255), description VARCHAR(255), date DATETIME ) WITH ( LOCATION = 'silver/data-demographie-france/French_cities_2005.csv', DATA_SOURCE = silver_datalake, FILE_FORMAT = csv_format );

Or if this file is meant for demographie_gold, and CitiesofFrance-VillesdeFrance.csv is missing, then you should remove or fix the habitant_gold table reference until that file is actually uploaded.

Path should not start with a forward slash / – This is important in serverless SQL pool when specifying LOCATION in external table definitions.

I hope this information helps!
Mehdi SAADI 0 Reputation points

2025-04-15T18:17:01.35+00:00

Hello Ganesh,

Thank you again for your message.

I followed your instructions, but I still have the same error. I tried to remove the ‘Credential’ part, since I'm using ‘Managed Identity’ with Storage Blot Contributor. Here are some screenshots of my various SQL scripts and IAM accesses.
Mehdi SAADI 0 Reputation points

2025-04-15T18:19:53.6133333+00:00

Hello Ganesh,

Thank you again for your message.

I followed your instructions, but I still have the same error. I tried to remove the ‘Credential’ part, since I'm using ‘Managed Identity’ with Storage Blot Contributor. Here are some screenshots of my various SQL scripts and IAM accesses.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-16T15:28:41.5566667+00:00

Hello @@Mehdi SAADI,

You can follow the below steps to create an external table from the synapse workspace:

Navigate to you data section and to your ADLS gen2 and pointing to the file like shown in the below image:

Next, you will have a window for the new external table:

After you click on continue here you will get an option to select the serverless db and your table name

Once you click on the open script you will be able to query the data from the file.

That includes for example

SELECT TOP 100 * FROM dbo.dilipext

Also as you mentioned that you are using the Manged identity

Make sure you also provide the Storage blob contributor role to you Manged Identity like below:
Mehdi SAADI 0 Reputation points

2025-04-16T17:46:16.52+00:00

Hello @Dileep Raj Narayan Thumula

Thank you for your message.

The problem is that I don't use ADLS gen2 but Azure Blob Storage. So on ADLS gen2, my database is empty.

The problem is that I'd like to connect it to my datalake database on my Azure Blob Storage.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-17T08:34:19.6633333+00:00

Hi @Mehdi SAADI,

Thank you for the reply and confirming I have tried some approach which might help you.

Hi

As you mentioned that you are using the Azure blob storage.

I have tried the same at my end below is the srcipt for my sample external table:

CREATE DATABASE Serverlessdb02 Use serverlessdb02 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Diliprajnarayan@2' CREATE DATABASE SCOPED CREDENTIAL credentialobject20 WITH IDENTITY ='SHARED ACCESS SIGNATURE', SECRET ='<**YOUR SAS TOKEN**>' GO; CREATE EXTERNAL DATA SOURCE demoDatasource WITH( LOCATION = 'https://blbaz02.blob.core.windows.net/' , CREDENTIAL = credentialobject20 ); CREATE EXTERNAL FILE FORMAT F1_fileformat_csv WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2, USE_TYPE_DEFAULT = TRUE ) ); CREATE EXTERNAL TABLE dilipext12 ( [Player Name] varchar(50), [Matches] varchar(500), [Runs] INT, [Average] DECIMAL(5,2) ) WITH( LOCATION ='/dilipf1/Australian_batsmen (1).csv', DATA_SOURCE = demoDatasource, FILE_FORMAT = F1_fileformat_csv ) GO

I am thinking the ERROR is because of the EXTERNAL DATA SOURCE because in your external table you are using the below

But you need to use something like this

'https://blbaz02.blob.core.windows.net/'

Also please note that I am also having storage blob contributor role

Results:

select * from dilipext12
Mehdi SAADI 0 Reputation points

2025-04-17T08:57:15.22+00:00

Hello @Dileep Raj Narayan Thumula

Thank you for your message.

I am using an abfs link as I need to specify the Azure storage account and container. Otherwise Synapse can know where to create the data?

in my link:

'abfss://******@talendazurestoragee.dfs.core.windows.net/silver'

datalake corresponds to my container and talendazurestoragee is my azure storage account.

Also, I don't use a credential because I've added a Managed Identity to my Synapse syn11 space with an Azure Storage Contributor role in the IAM Access Control tab.

Where do you think the problem lies?
Mehdi SAADI 0 Reputation points

2025-04-17T09:52:18.5966667+00:00

I use a new query :

But I always come across the same mistake with this data source :

With the same csv format :

And here is the table I created :

But I keep getting the same error, even though I'm using Managed Identity with my synapse syn11 space in Azure Blob Storage Contributor...
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-21T11:38:32.29+00:00

Hello @Mehdi SAADI,

Could you please check and share me the image if you have the storage blob contributor access to your managed identity.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-22T11:29:58.76+00:00

Hello @ Mehdi SAADI,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dileep Raj Narayan Thumula 0 Reputation points Microsoft External Staff

2025-04-23T12:21:37.24+00:00

Hello @ Mehdi SAADI,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Error : External table 'habitant_gold' is not accessible because location does not exist or it is used by another process.

Your answer