OLE DB or ADC error: [outsource.error] An error happened while reading data from the provider

[gsalas]

Hi All - I connect to two different servers when I run my power bi queries.  The issue is that when I run a data refresh (run daily) I am consistently getting stuck at one particular table as part of the refresh: "OLE or ODBC error: [DataSource.Error] An error happened while reading data from the provider: 'A transport-level error has ocurred when receiving results from the server.  (provider: TCP Provider, error: 0 - An existing connectionw as forcibly closed by the remote host.)'

My DA is telling me that the issue is the third party software that Power Bi is using to access the database.  I do not get this error if I manually refresh each table individually, but that takes forever.  Please help!

 

[hugoberry]

MaxDegreeOfParallelism - degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. There should be a server wide setting, however in some scenarios this might work as an overwrite.

MultiSubnetFailover- Is useful if you have an SQL Server Always On Availability cluseter. Here is what MSDN has to say about this option in the connection string:

"Always specify MultiSubnetFailover=True when connecting to a SQL Server 2012 availability group listener or SQL Server 2012 Failover Cluster Instance. MultiSubnetFailover enables faster failover for all Availability Groups and or Failover Cluster Instance in SQL Server 2012 and will significantly reduce failover time for single and multi-subnet AlwaysOn topologies. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, will aggressively retry the TCP connection."

[v-ljerr-msft]

Hi @gsalas,

To troubleshoot and narrow down the issue, could you try making a copy of your pbix file, and delete the particular table which raises the refresh issue, to see if the "Refresh All" works or not?(Note: Make sure you're using the latest version of Power BI Desktop):smileyhappy:

In addition, are you using the ODBC connector to get data from your servers in Power BI Desktop? If so, what provider are you using? 

Regards

[gsalas]

I think it's an ODBC connector.  I have no idea. I just log into the SQL Server via Power Bi.

I deleted the tables as a test and the refresh works perfectly.  But that isn't a solution.  I HAVE to have the other two problem  tables in my report - non negotiable.

[blopez11]

Just so I understand

Do the error happens in Power BI service when a refresh is scheduled?

If so, did it just start happening or has it always happened

Can you refresh your data with no issie in Power BI Desktop?

You said you connect to two different SQL servers

Is the issue only happening for one of the servers?

Sorry for all the questions, just trying to understand in case I can help

[gsalas]

Do the error happens in Power BI service when a refresh is scheduled?

These are manual refreshes in Power Bi Desktop only

If so, did it just start happening or has it always happened

It has always happened, but not every single time.  50/50

Can you refresh your data with no issie in Power BI Desktop?

No - Power Bi Desktop is where I get the error message

You said you connect to two different SQL servers

Is the issue only happening for one of the servers?

Yes - the issue only happens on the secondary server.

[blopez11]

Interesting.  If you are connecting through Get Data->SQL Server, then likely you are using the canned provider installed with Windows.  So while it could be the cause, I doubt it

If you can pull data from one sql server and not another, I don't think it is an issue with the data provider

I'm wondering if it is a networking issue (firewall) between your desktop and the server

Are the sql servers installed on physically different servers or virtual machines, or are they on the same physical server?

Can you preview the data from the bad sql server in the query editor?

[gsalas]

Confirming that I do indeed use the Get Data -> SQL Server interface.

The SQL Servers are located on physically different machines in completely different cities.  The one in Toronto (primary server) works fine.  The one in Atlanta (secondary server) is the one I have trouble with.  

Data preview works fine on the problem tables.  I can also manually refresh the table in question from the "Tables" panel in the Canvas screen (Ellipses --> Refresh Data).  The only time the query fails on the secondary (Atlanta) server is when I refresh the entire report at once.

[blopez11]

If you are able to see the data in the query editor, then I really doubt the data provider is the issue

I don't know your proximity to the Atlanta or Toronto server, so not sure whether query latency would be the issue

However, it seems that somewhere along the path to the Atlanta server, something is terminating the connection

I can't say for sure, but it seem that a rounter or network appliance is terminating the tcp connection, maybe b/c the connection is idle longer than some configured timeout

I would recommend possibly having one of your network guys install fiddler or wireshark on your desktop to maybe gain some insight into the network traffic from/to your desktop to the Atlanta server

Sorry I can't be of any further help,

[gsalas]

Thanks for your help.  Unfortunately, we don't really have a network "guy."  Just my DA who says that the SQL Server is runing the queries fine.  The servers are leased, so we have little to no control over the connectivity settings.  Hopefully Microsoft will build some kind of "buffering" into the report refreshes function so that an interrupted connection doesn't kill the whole refresh.

[hugoberry]

You can play with various options in the Sql.Database access data function in your power BI query.

Loooking at the MSDN for this function https://msdn.microsoft.com/en-us/library/mt260902.aspx there are plenty options to choose from. My favourite ones are CommandTimeout, MultiSubnetFailover and MaxDegreeOfParallelism.

[gsalas]

I had some success increasing the command timeout to 30 minutes, but I am still getting the error message.  What do the other two commands mean and what setting would you recommend? 

[gsalas]

I think the ComandTimeout function was the key.  I have set it to 30 minutes and now everything is loading smoothly.  Thanks!

[Anonymous]

Hi.. thanks for mentioning your solution. Can you help me understand how to set the commandTimeout to greater value. I am facing same issue with my Power bi desktop reports.

Thanks

[luisbio]

Hi viralmehta9 - I ran into the same issue a couple of minutes ago and managed to update the Command Timeout following these steps:

1) On the query Editor, select the query you wish to update.
2) Click on the Source's Gear Icon
3) Enter the desired command timeout minutes

I hope this helps!

[Anonymous]

Thanks @luisbio . I had shifted my approach. But thank you for response.

[AlaaRad]

its shame no one answered this gentlman 😭

[blopez11]

That is awesome, and great information to know