question

BryanWang-3107 avatar image
1 Vote"
BryanWang-3107 asked PRADEEPCHEEKATLA-MSFT commented

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

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

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image
2 Votes"
PRADEEPCHEEKATLA-MSFT answered RKP-9204 commented

Hello @BryanWang-3107,

Welcome to the Microsoft Q&A platform.

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

79575-synapse-selecttop100rows.gif

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.

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @PRADEEPCHEEKATLA-MSFT.

  • Regarding the "Select TOP 100 rows" in the context menu, the file I was trying to load is indeed a csv file, but the file extension is txt instead of csv. I thought the tool would be able to recognize the file format based on the content instead of the extension. After changing the file extension to csv the option is now showing up.

  • Regarding the file access issue, the workspace already has the 'Storage Blob Data Contributor' role to the storage account, and I already have the 'Contributor' role (which I believe is a role with higher access level) to the account and I thought it should be enough (since I can read/write blobs to the container). After I add myself to the 'Storage Blob Data Contributor' role, the issue is resolved. Does that mean the serverless SQL pool only makes use the 'Storage Blob Data Contributor' role even though the user might be in a role with higher access level?

Thanks again for the help.



0 Votes 0 ·

Hello @BryanWang-3107,

Yes, you need to have Storage Blob Data Contributor: Use to grant read/write/delete permissions to Blob storage resources.

1 Vote 1 ·
dev4zure avatar image
0 Votes"
dev4zure answered PRADEEPCHEEKATLA-MSFT commented

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?

161345-image.png



image.png (45.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @dev4zure,

Since this thread is too old, I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.

0 Votes 0 ·