Fabrick - write to lakehouse - get error 24596 when accesssing table using sql afterwards

[DavidResolvo]

Sql error number: 24596. Error Message: Failed to complete the command because the underlying location does not exist. Underlying data description: table 'dbo.Source_D365_LH_CustInvoiceTrans', file 'https://onelake.dfs.fabric.microsoft.com/9de3f770-392b-41e5-b6e4-d23d6f17560d/77ec4415-7fc0-43e3-841...'.

Observation is:

I load the data to the parquet file using a copy command.

The second afterwards I am trying to read data using the sql endpoint. And get the above error message.

If I wait a couple of minutes some metadata will be updated automatically and the select query will perform fine.

Any chance I can update the metadata?

br David

[Airscape]

I am having this same problem. Was there ever a solution?

[rubenstolk]

Hi,

We have the same problem. We copy files from a datalake to our lakehouse and save them as parquet every night by using a pipeline with the copy command. A few hours later we start refreshing a dataset which uses the SQL endpoint of that lakehouse as a source. Allmost every night one of the many tables in the lakehouse are not accessible using the endpoint. When I open the lakehouse in powerbi service and click the table (not using the enspoint but directly in the lakehouse) I do see the data. When I query the endpoint at that moment, I get error :

Msg 24596, Level 16, State 2, Line 1
Failed to complete the command because the underlying location does not exist. Underlying data description: table 'dbo.raw_PurchTable', file 'https://onelake.dfs.fabric.microsoft.com/6806fca9-b8fb-4155-a875-30803927ff8c/849c807e-96b6-43a8-a12...'.

I discovered that the name of the parquet file mentioned was the old name (from the night before). So something in updating the sql endpoint is going wrong. Sometimes even after a few hours it is still not updated. Only remedy is running the pipeline again. But sometimes it fixes itself after some time.

Please help!

Kind regard,

Ruben

[v-nikhilan-msft]

Hi @DavidResolvo @rubenstolk 

We'd like to check if it's our issue or hit the error by design. 

You can send us the information through email to AzCommunity[at]Microsoft[dot]com with the below details :

1. Workspace id
2. Workspace name
3. Artifact id

It will be great if you can also share the code snippet along with it. We would like to understand why there is an issue?

Subject of the email: ATTN: PRADEEP - Thread title

Thanks.

[ashafaa]

I also sent an email beacuse I have the same problem. Please help, thank you

[v-nikhilan-msft]

Hi @rubenstolk ,

Thanks for sharing the details over an email. We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

[v-nikhilan-msft]

Hi @rubenstolk ,
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

[rubenstolk]

I'm wondering where you read that my issue got resolved. Issue still persists and this week it hapens allmost every night. 

Fout in de gegevensbron: [ValidateMarkupTags][ccon]DataSource.Error: Microsoft SQL: [ccon]Failed to complete the command because the underlying location does not exist. Underlying data description: table 'dbo.raw_AR_Projbudgetline', file 'https://onelake.dfs.fabric.microsoft.com/6806fca9-b8fb-4155-a875-30803927ff8c/849c807e-96b6-43a8-a12...'. Statement ID: {38224CEF-930E-4591-983F-44870FC23D73} | Query hash: 0x7296E3FF80C7D54D | Distributed request ID: {5A857015-E6BC-4263-B26E-188730FF957F}[/ccon]. DataSourceKind = [ccon]SQL[/ccon]. DataSourcePath = [ccon]i6atfso675fepdqlwntpicilx4-vh6am2h3xbkudkdvgcadsj77rq.datawarehouse.pbidedicated.windows.net;ec28af42-919a-4fdc-9e99-7fc0c94d7502[/ccon]. Message = [ccon]Failed to complete the command because the underlying location does not exist. Underlying data description: table 'dbo.raw_AR_Projbudgetline', file 'https://onelake.dfs.fabric.microsoft.com/6806fca9-b8fb-4155-a875-30803927ff8c/849c807e-96b6-43a8-a12...'. Statement ID: {38224CEF-930E-4591-983F-44870FC23D73} | Query hash: 0x7296E3FF80C7D54D | Distributed request ID: {5A857015-E6BC-4263-B26E-188730FF957F}[/ccon]. ErrorCode = [ccon]-2146232060[/ccon]. Number = [ccon]24596[/ccon]. Class = [ccon]16[/ccon]. State = [ccon]2[/ccon]. [/ccon]. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Projectbudgetregels.

On different tables. Problem is still that the SQL endpoint doesn't know about the new filenames with new (by fabric generated) GUID's. 

[v-nikhilan-msft]

Hi @DavidResolvo ,
Thanks for using Fabric Community.

SQL error number 24596 means that the database is trying to access a file that does not exist. In this case, the database is trying to access the table dbo.Source_D365_LH_CustInvoiceTrans, but the file does not exist.

This error can occur if the table was deleted and the database has not been updated yet.

Can you please explain me how are you creating this table?Are you using the COPY command to create this table? If yes please send me the screenshot of the code. Where is the metadata getting updated?
This information can help me understand the issue better.
Thanks.

[DavidResolvo]

Hello sir. Thanks for replying so fast.

We are using the Fabric copy function. 

As for your queston: "Where is the metadata getting updated?" - 

the symptom is, that I update the data in the lakehouse, and try assessing using the sql endpoint the second after - I get the error 24596. If I wait a minute or two, then it works as expected.  

Again thanks for helping. It is apprecidated.

br David

[rubenstolk]

We are using the same method as @DavidResolvo :

[v-nikhilan-msft]

Thanks for the updates @DavidResolvo , @rubenstolk ,

Apologies for the issue you have been facing. 

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Appreciate your patience.

[v-nikhilan-msft]

Hi @DavidResolvo ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Please let me know if you have any other questions.