Forum Discussion
Fabrick - write to lakehouse - get error 24596 when accesssing table using sql afterwards
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-8418-b547c4e514b9/Tables/Source_D365_LH_CustInvoiceTrans/07471e8c-1637-412d-aacb-3a6a3753162f.parquet'.
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
12 Replies
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-a128-9af6f623f336/Tables/raw_PurchTable/99cf2c7a-f549-4495-8dce-5265b17696ad.parquet'.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
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 :
- Workspace id
- Workspace name
- 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.
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.
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.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
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.
I am having this same problem. Was there ever a solution?
