unable to select from .csv

arkiboys 9,666 Reputation points
2022-08-20T09:33:01.127+00:00

hello,
in data section of synapse workspace, inside the gen2 where I have a container which holds csv files, I right click on the file and select new sql script.
The below sql is generated but see the error at the bottom of it when I try to execute it

-- This is auto-generated code
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK 'https://storagegen2Name.dfs.core.windows.net/raw/foldername/company/samplefile.csv',
FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
) AS [result]

ERROR when trying to run the above select:

File 'https://storagegen2Name.dfs.core.windows.net/raw/foldername/company/samplefile.csv' cannot be opened because it does not exist or it is used by another process.

Any thoughts?
Thank you

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,484 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dillon Silzer 54,831 Reputation points
    2022-08-21T04:41:08.527+00:00

    Hi @arkiboys

    Check out this similar post https://learn.microsoft.com/en-us/answers/questions/321814/file-cannot-be-opened-because-it-does-not-exist-or.html

    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.

    Ensure you have Storage Blob Data Contributor role (Use to grant read/write/delete permissions to Blob storage resources).

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

    If this is helpful please accept answer.

    0 comments No comments

0 additional answers

Sort by: Most helpful