Date Load errors

[DanielDiAngelo]

for some time we have receieved intermittent TDS Protocol errors from our online data refreshes..  but a few weeks ago everything came off the rails.  here is the setup:   Personal gateway, feeding 15-20 data sets with upwards of 100 queries..been working fine for more than 3 yrs.  2 wks ago, both the gateway feeds and the desktop feeds have died for at least half of those data sets.  all the queries are setup as import.  the errors now read:  

An error happened while reading data from the provider: 'Invalid operation. The connection is closed.' 

OR 

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 - An existing connection was forcibly closed by the remote host.)'

OR

Microsoft SQL: Protocol error in TDS stream

These errors are the online codes.  in desktop we also get:

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. An unexpected exception occurred.

OLE DB or ODBC error: [DataSource.Error] An error happened while reading data from the provider: 'Invalid operation. The connection is closed.

These errors happen 99% of the time.  with the very rare random successful run..

What hasnt fixed it:  rolling back to august desktop

tweaking queries to remove one table worked for a minute, then didnt again. Disabling refresh on failing queries worked for one file.  but same query in different file did not work.  for clarity these are 20 item selects with just 3 tables joined from a SQL database.

Any help Please.  What do we try next..  

[Anonymous · Accepted solution]

@DanielDiAngelo 

Have you try update the ODBC connector and Personal gateway to new versions.

MySQL :: Download Connector/ODBC

Power BI Gateway | Microsoft Power BI

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[bir1concepts]

I got the same problem, do you have any updates?

[DanielDiAngelo · Accepted solution]

YES!  it was a networking issue.  We had a switch that was limiting throughput.  The common feature to our failing queries was record counts.  The big stuff would fail.  Infrastructure opened up the bandwidth filter and we have been stable since.  We are usinging a personal gateway so everything goes through our local network on its way to the MS servers.  

Good luck.

[robinhood276]

I'm having the same issue as well. How do you open up the bandwith filter to let more data to load? I'm using my work laptop to refresh which is where my on premise gateway is.

[DanielDiAngelo]

It was a firewall in our network hardware.  our infrasctructure team is who handled the fix.  if you are responsible for the hardware also, try searching the internet for your model/software.

Note a laptop for auto refreshing isnt always the best, as the gateway needs to be on the network when doing the updates..  wifi, VPNs etc are all now in play as pinch points if you are not hardwired.

[Anonymous · Accepted solution]

@DanielDiAngelo 

Have you try update the ODBC connector and Personal gateway to new versions.

MySQL :: Download Connector/ODBC

Power BI Gateway | Microsoft Power BI

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[DanielDiAngelo]

All items updated.  No change in behavior.  

We have been successfull in getting some queries to run by removing a table.  not a specific table, just a table.  i feel like our sql server may have seen an update and now some joins are causing issues..  they arent complicated joins.  Worse yet i had some working, and now they are failing.. is there some kind of log that is filling up somewhere?

Thanks for any suggestions.