Forum Discussion

ShivaPr's avatar
ShivaPr
Icon for Advocate I rankAdvocate I
23 days ago
Solved

when selecting table from Azure Synapse dedicated SQL pool

Hi Team, I’m connecting Power BI Desktop to an Azure Synapse dedicated SQL pool. The connection succeeds and I can see the database, but when I select the table dbo.BD, I get this error: Can an...
data engineering
  • jubinsoni's avatar
    jubinsoni
    21 days ago

    Hi ShivaPr,

    The path and credential checks above are the right starting point. A few extra things I would look at, because error 16562 says "location does not exist or it is used by another process", and that second half is often the real cause.

    First, confirm the object chain. Power BI selects dbo.BD, but the error names dbo.BDRatesHourlyRate, so dbo.BD is most likely a view over that external table. Run this in Synapse Studio with the same login Power BI uses:

    -- what does dbo.BD depend on?
SELECT OBJECT_NAME(referencing_id) AS referencing_object,
       referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.BD');

-- where does the external table point?
SELECT t.name, t.location, ds.name AS data_source,
       ds.location AS data_source_location
FROM sys.external_tables t
JOIN sys.external_data_sources ds
  ON t.data_source_id = ds.data_source_id
WHERE t.name = 'BDRatesHourlyRate';

-- can this login read it directly?
SELECT TOP 10 * FROM dbo.BDRatesHourlyRate;

    Then check these less obvious causes:

    a load (ADF / pipeline / Spark) overwrites the folder
  while Power BI queries it -> files briefly missing
LOCATION folder is empty, or only holds files starting
  with _ or . (treated as hidden by PolyBase)
storage firewall blocks Synapse -> "Allow trusted
  Microsoft services" off, or the workspace managed
  identity lacks Storage Blob Data Reader

    The hidden-file behavior and the LOCATION rules are described in the CREATE EXTERNAL TABLE documentation.

    If the timing explanation fits, the most robust fix for Power BI is not to read the external table directly at all. I would land the data in an internal table after each load and point Power BI there:

    -- run after each load completes
IF OBJECT_ID('dbo.BDRatesHourlyRate_curated') IS NOT NULL
    DROP TABLE dbo.BDRatesHourlyRate_curated;

CREATE TABLE dbo.BDRatesHourlyRate_curated
WITH (DISTRIBUTION = ROUND_ROBIN,
      CLUSTERED COLUMNSTORE INDEX)
AS
SELECT * FROM dbo.BDRatesHourlyRate;

    That removes the storage dependency from the Power BI refresh, avoids reads while files are being overwritten, and is usually much faster in a dedicated SQL pool.

    So my overall approach would be:

    confirm the view -> external table chain
-> rule out concurrent writes and empty/hidden folders
-> verify firewall and managed identity access
-> serve Power BI from an internal table

    That way Power BI reads a stable table, and the external table stays a staging layer only.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

v-csrikanth's avatar
v-csrikanth
Icon for Community Support rankCommunity Support
23 days ago

Hi ShivaPr​ 

The screenshot shows SQL error 16562 for the external table dbo.BDRatesHourlyRate. Power BI can connect to the database and read its metadata, but that does not confirm that the SQL pool can access the underlying storage files. The wording “used by another process” does not, by itself, prove that a file is locked.

Also, the error names a different object from dbo.BD. If dbo.BD is a view, it may reference this external table.

First, connect through SSMS or Synapse Studio to the same endpoint and database, using the same identity as Power BI, and run:

SELECT TOP (1) * FROM dbo.BD;

If the same error occurs there, investigate the external-table/storage configuration rather than changing the Power BI connector.

Ask your database administrator to inspect the failing external table’s existing configuration:

SELECT et.name AS ExternalTable, et.location AS TableLocation, ds.name AS DataSourceName, ds.location AS DataSourceLocation, ds.type_desc AS DataSourceType, c.name AS CredentialName, c.credential_identity AS CredentialIdentity FROM sys.external_tables AS et JOIN sys.external_data_sources AS ds ON et.data_source_id = ds.data_source_id LEFT JOIN sys.database_scoped_credentials AS c ON ds.credential_id = c.credential_id WHERE et.object_id = OBJECT_ID(N'dbo.BDRatesHourlyRate');


The Azure-side checks are:

  1. Storage location: Confirm that the external data source’s base location and the table’s relative location resolve to the correct container and folder/files. Check path casing, moved/deleted files, and whether a pipeline is replacing the files while they are queried.
  2. Credential and storage permissions: Verify the credential attached to that external data source. If it uses a managed identity, check storage access for that identity, not just your own account. For read-only access, Storage Blob Data Reader at the appropriate scope is one option. If access is ACL-based, verify file-read, directory-listing and parent-directory traversal permissions. For a key/token-based credential, check validity and applicable permissions.
  3. Storage networking: Check whether the storage firewall permits access from the dedicated SQL pool. Successful Power BI-to-Synapse connectivity does not establish Synapse-to-storage connectivity. Do not assume that a private endpoint used by a Spark job or pipeline automatically provides the SQL pool’s storage access.
  4. SQL permissions: Confirm that the Power BI identity has SELECT permission on the required table/view. A successful test using a different administrator account does not rule out an identity-specific issue.

Once the query succeeds with the Power BI identity, retry the table preview. If it succeeds in SSMS but still fails in Power BI, compare the endpoint, database and authentication settings between the two clients.

Thanks.