Cannot refresh a table in SSMS through xmla endpoint

[workingpbi]

I'm trying to refresh a table in Power BI dataset through XMLA endpoint in SSMS. I'm getting this error. "Stream does not support reading. The remote server returned an error: (400) Bad Request." The screenshot is the exact error message I see. 

A few points:

- I can refresh this exact same table with fewer data, but when it's full data, it gives me the error.

- I'm using a sql native query for the table

Has anybody experienced this? What would be the cause?

Thanks in advance!

[pmreis]

Hi @workingpbi 

As you are able to refresh with a subset of data but not with the entire data, it seems you are experiencing a timeout. Here are a few things you can check:

1. Power BI Service Timeout:

   • In the context of Power BI Service, there are predefined timeout limits for various operations, and these cannot be changed by end-users. For instance, dataset refreshes in Power BI Service have specific timeout limits based on the type of source and the service tier you're on.

2. Power BI Desktop Query Timeout:

   • In Power BI Desktop, you can adjust the timeout for specific data source queries.
     1. Go to File > Options and settings > Options.
     2. Under Current File, select Data Load.
     3. Adjust the Timeout value in minutes for the data source queries.
     4. Click OK.

3. SQL Server Management Studio (SSMS) Timeout:

   • If you're using SSMS to run queries or scripts:
     1. Open SSMS and connect to your database.
     2. Go to Tools > Options.
     3. Navigate to Query Execution > SQL Server > General.
     4. Adjust the Execution time-out value (in seconds). Setting it to 0 means queries won't time out.
     5. Click OK.

4. Power BI Gateway Timeout:

   • If you're using the On-Premises Data Gateway to refresh datasets in Power BI Service, there are configuration files where timeout settings can be adjusted. However, modifying these settings requires a good understanding of the gateway's configuration and can impact other datasets that use the same gateway.

5. Data Source Timeout:

   • If you're connecting to a specific data source (like SQL Server), there might be timeout settings on the data source side that you can adjust. For instance, SQL Server has a Remote Query Timeout setting that defines how long a remote query will run before timing out.

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn