File cannot be opened because it does not exist or it is used by another process in Azure Synapse workspace

Question

Hi,

I created a new Azure Synapse Analytics resource and uploaded some csv files to the data lake storage created with the Synapse. I can see the files in the workspace and preview the files.

However, when I tried to create a new SQL script from the file (right click on the file), I don't see the option "Select TOP 100 rows" to open a new script for to to edit and run the query. I only see the "Bulk load" option, and when I clicked it, I got the error "Failed to execute query. Error: File 'x' cannot be opened because it does not exist or it is used by another process. The batch could not be analyzed because of compile errors." If I created a new SQL script from the landing page (New -> SQL script) and tried to query the file, I got the same error.

Is this a permission issue? Since I can list the files in the container and preview them, I assume the workspace should have access to the files in data lake.

Thanks,
Bryan

Answer 1

Hello @Bryan Wang ,

Welcome to the Microsoft Q&A platform.

New SQL script -> Select TOP 100 rows works with the files with below format (csv, json, parquet).

If your query fails with the error saying, File cannot be opened because it does not exist or it is used by another process' and you're sure both file exist and it's not used by another process it means serverless SQL pool can't access the file. This problem usually happens because your Azure Active Directory identity doesn't have rights to access the file. By default, serverless SQL pool is trying to access the file using your Azure Active Directory identity. To resolve this issue, you need to have proper rights to access the file. Easiest way is to grant yourself 'Storage Blob Data Contributor' role on the storage account you're trying to query.

• Visit full guide on Azure Active Directory access control for storage for more information.
• Visit Control storage account access for serverless SQL pool in Azure Synapse Analytics

Reference: Query fails because file cannot be opened

Hope this helps. Do let us know if you any further queries.

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

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I followed MS Official tutorial, I am stuck at Analyze data with a serverless SQL pool, able to run query from synapse studio but not from SSMS. I cross checked my access, in addition to Storage Blob Data Contributor, I have given Storage Blob Data Owner role access to myself but I am getting this same error. Is there any step I missed to query from SSMS?

Answer 3

please share the new thread link or if you got success?

Answer 4

Add the role of "Storage Blob Data Contributor" to your storage account using IAM (Identity Access Management). Select "User, group, or service principal" and then select your default directory mail account and add role to it. It should definitely work!

Answer 5

Hello @Bryan Wang ,

Welcome to the Microsoft Q&A platform.

Answer to your question is very simple, below are the steps

1. Go to the storage account which is interacting with serverless sql pool of synapse
2. click on container
3. right click on container name and then click manage ACL
4. then allow read, write, execute permissions to all users
5. repeat this process for all directories and all files in that container including container itself

I hope this helps. Do let me know if you have any queries.

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.