Can't query partitioned tables - Azure Synapse Link for Dataverse

Leo Milos 10 Reputation points
2023-03-20T16:05:06.8433333+00:00

Hi, we have a problem with accessing partitioned tables in Azure Synapse Workspace that were previously created by ingesting tables from Power Platform via Azure Synapse Link for Dataverse. Snapshot folder and .csv files are normally created but can't be accessed via partitoned tables.

For some reason we get message : Invalid object name 'dataverse_********.dbo.opportunity_partitioned'.

Also there are no views created in Dataverse for Snapshot tables, we thought this should be automatically created when Creating Azure Synapse Link for Dataverse.

Thanks for the help!

invalid_object_error

db_no_views

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,485 questions
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,997 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Johan Hostens 15 Reputation points
    2023-04-13T06:12:28.8133333+00:00

    @Tzee Chen , This fixed the issue for me:

    1. Check if Workspace System Assigned Managed identity has Storage Blob Data Contributor access on the Storage Account. --> This was ok in my case
    2. Create a server-scoped credential on the master database for the storage account and container
    --On master database
    CREATE CREDENTIAL [https://storageaccount.dfs.core.windows.net/container]
    WITH IDENTITY='Managed Identity' 
    

    --> After creating the server-scoped credential the partitioned tables started working.

    3 people found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.