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

Leo Milos 10

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

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-03-21T18:30:00.9666667+00:00

Hello Leo Milos,

Welcome to the MS Q&A platform.

Can you please confirm if you have the issue on the specific partitioned or all partitioned tables?

Views supported to be created. Sometimes it will take time for the views to get created for the partitioned tables.
Leo Milos 10 Reputation points

2023-03-22T08:39:05.9533333+00:00

Hi,

we have an issue with all partitioned tables, they haven't been created and a couple of days passed since creating Synapse Link.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-03-22T18:27:40.7166667+00:00

Hello Leo Milos,

I had a chat with the product group, and they are looking for workspace details like name, region, issue start time, etc., to troubleshoot the issue further.

If you have a support plan, please open a support case so the support engineer can collect these details and work with the PG.

If you don't have a support plan, please let me know so I can enable one-time free support for you to work on this.

I am looking forward to hearing from you.
Johan Hostens 15 Reputation points

2023-03-23T09:47:02.8433333+00:00

I'm currently facing the exact same problem.
Setup: Dataverse synapselink standard configuration (in-place updates) // West-Europe

All files are created correctly (including the snapshot folders).
Near-realtime external table is working.
I also see an external table for the snapshot data. But when I try to query it, I get the same error.
I don't see any views in SSMS, so there seems to be something wrong there..

I unlinked and relinked again, but still have the same issue.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-03-23T16:42:07.56+00:00

Hello Johan Hostens,

Thanks for your comment. It seems like there was an issue in the metadata sync process responsible for syncing data from DataVerse to the Serverless instance.

To troubleshoot the issue further, we need workspace details like name, region, issue start time, etc.

If you have a support plan, please open a support case so the support engineer can collect these details and work with the PG.

If you don't have a support plan, please let me know so I can enable one-time free support for you to work on this.
Tzee Chen 0 Reputation points

2023-04-12T19:48:37.9+00:00

I am facing the exact same issue when querying partitioned tables, like what was mentioned by Johan Hostens. Has this issue being resolved?
David Prince 0 Reputation points

2023-04-28T08:53:36.2466667+00:00

I have the exact same problem, would really appreciate some help with this please?

I tried the "credential" solution below but this did not work.

We're able to query our non-partitioned tables fine, but the partitioned tables error with:

"Failed to execute query. Error: Invalid object name..."

Surely these should just work straight away as part of the Synapse link setup?
Ajit Gangurde (Tata Consultancy Services) 0 Reputation points Microsoft Vendor

2023-10-09T18:51:10.3033333+00:00

We are facing the same issue but its not for all partition table .

We have 20+ partition table for 6 partition table we are getting error Invalid object name.For rest of table we are able to access it without any issue .

1 answer

Johan Hostens 15 Reputation points

2023-04-13T06:12:28.8133333+00:00
@Tzee Chen , This fixed the issue for me:

Check if Workspace System Assigned Managed identity has Storage Blob Data Contributor access on the Storage Account. --> This was ok in my case

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.
Please sign in to rate this answer.

3 people found this answer helpful.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2023-04-13T16:41:17.6366667+00:00

Hi Johan Hostens, Thank you for sharing the resolution!

Johan Hamelinck (XV) 0 Reputation points

2023-05-08T13:28:05.4033333+00:00

Hmm can't get it to work.

I suppose I have to replace

storage account with my storage account

container with the container that is being used

and Managed Identity with the managed identity assigned to the Synapse workspace ?

Gary Melhaff - US 6 Reputation points

2023-09-15T20:58:06.2766667+00:00

Both of these are set for mine and still can't see the external tables.

Steinke, Andy 1 Reputation point

2023-09-18T20:28:43.56+00:00

Thank you for sharing this. I was running into the same issue and then it appears that creating the credential in the master database resolved it. Thank you very much for posting this.

Gary Melhaff - US 6 Reputation points

2023-09-18T20:32:46.16+00:00

Not for me. I can see them in Azure but not SSMS with same account. Must be something in addition that needs to be done.

Rafał Materek 0 Reputation points

2023-09-27T09:54:12.05+00:00

Thanks a lot Johan. Worked for me also!

Marisol Steinau 0 Reputation points

2024-03-11T14:31:40.8066667+00:00

Unfortunately for me it still does not work. I have created the credential like this (which works fine):

CREATE CREDENTIAL [https://mystorageaccount.dfs.core.windows.net/mycontainer]WITH IDENTITY='Managed Identity'

However when I try to query a partitioned table using the master databse int eh built-in serverless sql pool I get this error:

Content of directory on path 'https://presalestestusers.dfs.core.windows.net/dataverse-presalestest-unqf8fe93ad29b7471599034eb9c7323/logisticslocation/createdonpartition=/.csv' cannot be listed.

What am I doing wrong?

Gary Melhaff - US 6 Reputation points

2024-07-23T00:51:57.4366667+00:00

Curious I ran above and it said it executed but when I query from sys.database_scoped_credentials there's nothing there on master db. If I query from the database I created for export to datalake I see the scoped credentials there, not in master db.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

1 answer

Your answer