Framework Microsoft SqlClient Data Provider: Msg 13807, Level 16, State 1, Procedure vwtpmr_missions, Line 12 Content of directory on path '....../_delta_log/* cannot be listed.

Question

Framework Microsoft SqlClient Data Provider: Msg 13807, Level 16, State 1, Procedure vwtpmr_missions, Line 12 Content of directory on path '....../_delta_log/* cannot be listed.

JAWAHIR Ambrine 0

I keep getting the error below: However, I have already done the following:

Assign Storage blob contributor roles to users and azure synapse workspaces.
Assign ACL roles "Read, write execute" to users and azure synapse workspaces.
Check schema of delta_log files
Allow all network access

Framework Microsoft SqlClient Data Provider: Msg 13807, Level 16, State 1, Procedure vwtpmr_missions, Line 12 Content of directory on path 'https://dkdidataplatformeuwdls.dfs.core.windows.net/synapsedfs/datalake/silver/transports_urbains/TPMR/_delta_log/*.*' cannot be listed.
2023-08-04T06:37:10.2658708Z ##[error]Error SQL72045: Script execution error.  The executed script:
CREATE VIEW vwtpmr_missions
2023-08-04T06:37:10.2854109Z ##[error]AS
2023-08-04T06:37:10.3043086Z ##[error]SELECT [REFCON],
2023-08-04T06:37:10.3089795Z ##[error]       [JOUR],
2023-08-04T06:37:10.3175302Z ##[error]       [KMS_MISSION_DIRECT],
2023-08-04T06:37:10.3217783Z ##[error]       [KMS_MISSION_REEL],
2023-08-04T06:37:10.3317328Z ##[error]       [DUREE_MISSION_DIRECT],
2023-08-04T06:37:10.3429762Z ##[error]       [DUREE_MISSION_REEL],
2023-08-04T06:37:10.3528719Z ##[error]       [KMS_MISSION_HC],
2023-08-04T06:37:10.3612173Z ##[error]       [KMS_MISSION_VO]
2023-08-04T06:37:10.3702148Z ##[error]FROM   OPENROWSET (BULK 'https://dkdidataplatformeuwdls.dfs.core.windows.net/synapsedfs/datalake/silver/transports_urbains/TPMR/', FORMAT = 'DELTA') AS fct;
2023-08-04T06:37:10.3795893Z ##[error]System.Management.Automation.RemoteException

KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-04T23:00:54.9133333+00:00

@JAWAHIR Ambrine Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per the error message, my understanding is that you are using Azure Synapse serverless pool to query Delta format files and you got this error. Please correct me if I'm wrong.

Could you please confirm where did you assign Storage blob contributor role to users as well as Synapse workspace managed identity? Also please confirm, the Delta path provided in the query has delta_change_log file? If so please verify if the users and synapse workspace has access to that Delta log file.

Below doc says few possible root causes and resolution for the above error, kindly review one more time to ensure all prerequisites are followed.

Troubleshoot Serverless SQL Pools - Content of directory on the path can't be listed

Also please refer to this document and see if the all the required prerequisites are met: Access external storage using serverless SQL pool in Azure Synapse Analytics

Hope this info helps. Let me know how it goes.

Thank you
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-07T22:38:40.89+00:00

@JAWAHIR Ambrine checking to see if you have got a chance to see my previous response. If so, kindly share additional information as requested.

In case if the issue has already been resolved, please share the resolution steps so that it can be beneficial to other readers.
JAWAHIR Ambrine 0 Reputation points

2023-08-08T05:35:53.2166667+00:00

Thank you @KranthiPakala-MSFT ! However it is still not working.

Yes I need to query delta_log files in an external table using Azure synapse serverless pool.

I have assigned Storage Blob Contributor to the users etc in the storage account where the external table is found. The users and synapse workspaces have access to the delta_log file. I am trying to access as SQL User.

It is still unsolved.
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-09T22:48:16.85+00:00

@JAWAHIR Ambrine Thanks for your response. As you confirmed that all users are having Blob data contributor role as well as synapse has that role, I don't see any issues there unless we miss any user. Could you please file a support ticket for deeper investigation of this issue. In case if you dont have a support plan let me know.

Thank you
JAWAHIR Ambrine 0 Reputation points

2023-08-10T05:05:54.3833333+00:00

@KranthiPakala-MSFT I do not have a support plan
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-10T18:12:52.8366667+00:00
@JAWAHIR Ambrine Thanks for confirming.

For deeper investigation and immediate assistance, could you please send an email to AzCommunity[at]Microsoft[dot]com with the below details, so that we can enable a one-time-free support for you to work closely on this matter.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Subscription ID: <your subscription id>

Looking forward to your reply. Please do let me know here once the email is sent.

Thank you!

Your answer

KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-04T23:00:54.9133333+00:00

@JAWAHIR Ambrine Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per the error message, my understanding is that you are using Azure Synapse serverless pool to query Delta format files and you got this error. Please correct me if I'm wrong.

Could you please confirm where did you assign Storage blob contributor role to users as well as Synapse workspace managed identity? Also please confirm, the Delta path provided in the query has delta_change_log file? If so please verify if the users and synapse workspace has access to that Delta log file.

Below doc says few possible root causes and resolution for the above error, kindly review one more time to ensure all prerequisites are followed.

Troubleshoot Serverless SQL Pools - Content of directory on the path can't be listed

Also please refer to this document and see if the all the required prerequisites are met: Access external storage using serverless SQL pool in Azure Synapse Analytics

Hope this info helps. Let me know how it goes.

Thank you
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-07T22:38:40.89+00:00

@JAWAHIR Ambrine checking to see if you have got a chance to see my previous response. If so, kindly share additional information as requested.

In case if the issue has already been resolved, please share the resolution steps so that it can be beneficial to other readers.
JAWAHIR Ambrine 0 Reputation points

2023-08-08T05:35:53.2166667+00:00

Thank you @KranthiPakala-MSFT ! However it is still not working.

Yes I need to query delta_log files in an external table using Azure synapse serverless pool.

I have assigned Storage Blob Contributor to the users etc in the storage account where the external table is found. The users and synapse workspaces have access to the delta_log file. I am trying to access as SQL User.

It is still unsolved.
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-09T22:48:16.85+00:00

@JAWAHIR Ambrine Thanks for your response. As you confirmed that all users are having Blob data contributor role as well as synapse has that role, I don't see any issues there unless we miss any user. Could you please file a support ticket for deeper investigation of this issue. In case if you dont have a support plan let me know.

Thank you
JAWAHIR Ambrine 0 Reputation points

2023-08-10T05:05:54.3833333+00:00

@KranthiPakala-MSFT I do not have a support plan
KranthiPakala-MSFT 46,632 Reputation points Microsoft Employee

2023-08-10T18:12:52.8366667+00:00

@JAWAHIR Ambrine Thanks for confirming.

For deeper investigation and immediate assistance, could you please send an email to AzCommunity[at]Microsoft[dot]com with the below details, so that we can enable a one-time-free support for you to work closely on this matter.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Subscription ID: <your subscription id>

Looking forward to your reply. Please do let me know here once the email is sent.

Thank you!

Share via

Framework Microsoft SqlClient Data Provider: Msg 13807, Level 16, State 1, Procedure vwtpmr_missions, Line 12 Content of directory on path '....../_delta_log/* cannot be listed.

Your answer