MS SQL server timeout

[Patamawadee]

we have following scenario:

a) On-Premise Data Gateway is installed on an app server,

b) MS SQL as a source data is geographically elsewhere,

c) 5 reports published on PBI service, each max 4-5 MB

d) setting timeout 120 mins

e) setting privacy level = private

All ports from app server allowed to Azure, as well Azure data center reachable from SQL server. App server allowed to communicate on port 1433 towards SQL server.

Any ideas please, what went wrong?

Error : Report_XXX has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

Type or error: Unable to connect to the data source undefined.

Underlying error code:	-2147467259
Underlying error message:	Microsoft SQL: Query timeout expired
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	SQL
Microsoft.Data.Mashup.ValueError.DataSourcePath:	XXXX;XXXX
Microsoft.Data.Mashup.ValueError.ErrorCode:	-2147217871
Microsoft.Data.Mashup.ValueError.Message:	Query timeout expired

Thank you very much for help!

[v-yingjl]

Hi @Patamawadee ,

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li

[lbendlin]

d) timeout setting is not what you think it is. It is only the connection timeout. There is currently no way to specify the query timeout.

Workaround:  Optimize your SQL queries to run faster. Indexes, statistics, query plans etc.

[Patamawadee]

I used to try just cutting, by using "where" in my query sql. I found that it can refresh well.
It's not about the data storage limit, right? I am currently using powerbi pro.

[v-yingjl]

Hi @Patamawadee ,

Based on your description, you had about 5 reports in power bi service, did all of them have the same error or just one of them have this error?

Can you refresh these reports successfully in power bi desktop manually?

According to the error message, seems like the datasource is not matched with your report and the query in datasource is timeout. If you can refresh them in power bi desktop successfully, maybe you can delete related reports and datasets in power bi service and try to republish them to service to try it again.

When you configure gateway in service, please check the datasource is matched with your reports.

You can also refer these simliar issues that could help you:

1. MS SQL server timeout 
2. Refresh Failed - Internal Service Error 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.