How to fix the following error: "failed:Query failed because of compute container failures or other transient errors, and retry exhausted. This can be an intermittent issue. Please retry later."

Question

I have a Data Lake setup in Azure with synapse on top to connect using sql (management studio, pyodbc, power BI etc.).
Everything has been working fine until now every time I execute a specific query I get the following error message: "failed:Query failed because of compute container failures or other transient errors, and retry exhausted. This can be an intermittent issue. Please retry later."

When doing some tests in Microsoft SQL Server Management Studio, I found that I can query directly to the data lake using SELECT *
FROM OPENROWSET (
BULK ='dataset', DATA_SOURCE = 'container' ) as rows

to get the raw data. However, as soon as i try to add some additional logic to the query I get the above error. I can make new columns by eg. casting datetimes to dates and so on which works fine, but when I try to use the LEAD function to make a new column I get the error. The error persists when querying from python instead of management studio so it must be something with the SQL Database. What is going on here? Is this something with Synapse?

Answer 1

Hi @Anonymous ,

Welcome to Microsoft Q&A platform and thanks for posting your question.

Based on the error message you received, it seems like the query failed due to compute container failures or other transient errors. This can be an intermittent issue, so it's recommended to retry the query later.

Regarding your query, it seems like you are able to query the data lake directly using OPENROWSET, but encounter issues when adding additional logic to the query. Specifically, when you try to use the LEAD function to create a new column, you receive the error message.

One possible reason for this issue could be related to the amount of data being processed by the query. The LEAD function is a window function that requires sorting and grouping of data, which can be resource-intensive. It's possible that the query is exceeding the resources available in the compute container, leading to the error message.

To resolve this issue, you can try optimizing your query to reduce the amount of data being processed. For example, you can try filtering the data before applying the LEAD function or using a smaller dataset for testing purposes. Additionally, you can try increasing the resources available in the compute container to see if that resolves the issue.

If the issue persists, you can try reaching out to Microsoft support for further assistance. They can help you diagnose the issue and provide guidance on how to resolve it.