For the last 5 months we have been unable to refresh our datasets each morning using Scheduled refresh and our on premise Gateway. Our datasets mostly pull information from our MSSQL database where I have created Views especially for PowerBI. There is around 12 datasets and there appears to be no pattern as to which one does not refresh on a particular day or time. There eappears to be no pattern in the number of datasets that do no automatically refresh either.
We have tried the following:
- Scheduling the refresh for different data sets at different timings where there will be no collisions occur between the refreshes
- Configuring another Gateway in the same Domain and add some datasets to it
- Reducing the data in the larger datasets
- Testing all views directly in the database where they all run in less than 1 minute
- Trying datasets where there are not too many calculations in PowerBI
Regardless of the above, the 12 datasets do not refresh daily as scheduled. My workaround is to manually refresh the failed datasets each morning and they will evvenutally refresh after 1 - 3 attempts.
Here are the most common error messages we receive:
An error happened while reading data from the provider: 'A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)'
Microsoft SQL: Protocol error in TDS stream
Just wondering if anyone else has experienced similar problems and came up with a solution?
Secondly, has anyone ever build something that will automotically keep attempting to refresh a failed refresh over and over until is successfully refreshes?
