Reply
Super Contributor
Posts: 1,026
Registered: ‎12-29-2015
Accepted Solution

Azure Transport-Level Error

[ Edited ]

I'm trying to connect to an Azure SQL database and everything is working fine except on one table. When I try to select that table and edit the query, it stalls for a while and then eventually gives me the following error:

 

DataSource.Error: Microsoft SQL: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Details:
    DataSourceKind=SQL
    DataSourcePath=thisisafakeurl.database.windows.net;NameOfDatabaseGoesHere
    Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
    Number=64
    Class=20

It only happens on this one table. Everything else is just fine. It happens to be the largest table in the database, both in terms of rows and columns.

 

I have seen a suggestion that disabling "Enable parallel loading of tables" in the Data Load Options panel would fix this problem. Initially it did, and disabling that does allow other queries on this table that I've already written to refresh (queries that restrict the number of rows and columns). However as of this morning the error is back whenever I try to load a fresh preview to begin a new query. I know two or three more columns were added to the table yesterday.

 

So is there some timeout setting or something that can be changed on Azure to solve this? I think I've run out of Power BI settings to turn off.


Accepted Solutions
Moderator
Posts: 3,393
Registered: ‎03-10-2016

Re: Azure Transport-Level Error

Hi @KHorseman,

I would recommend you firstly connect to the Azure SQL Database in SQL Server Management Studio(SSMS), then select the large table to check your desired fields. After excuting an appropriate query to get data in SSMS, apply this query statement in Power BI Desktop.

Thanks,
Lydia Zhang

View solution in original post


All Replies
Super Contributor
Posts: 1,026
Registered: ‎12-29-2015

Azure Transport-Level Error

I'm trying to connect to an Azure SQL database and everything is working fine except on one table. When I try to select that table and edit the query, it stalls for a while and then eventually gives me the following error:

 

DataSource.Error: Microsoft SQL: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Details:
    DataSourceKind=SQL
    DataSourcePath=thisisafakeurl.database.windows.net;NameOfDatabaseGoesHere
    Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
    Number=64
    Class=20

 

It only happens on this one table. Everything else is just fine. It happens to be the largest table in the database, both in terms of rows and columns.

 

I have seen a suggestion that disabling "Enable parallel loading of tables" in the Data Load Options panel would fix this problem. Initially it did, and disabling that does allow other queries on this table that I've already written to refresh (queries that restrict the number of rows and columns). However as of this morning the error is back whenever I try to load a fresh preview to begin a new query. I know two or three more columns were added to the table yesterday.

 

So is there some timeout setting or something that can be changed on Azure to solve this? I think I've run out of Power BI settings to turn off.

Moderator
Posts: 3,393
Registered: ‎03-10-2016

Re: Azure Transport-Level Error

Hi @KHorseman,

How do you connect to Azure SQL database from Power BI Desktop? Using import or DirectQuery mode? Is there any possibility that you can separate the large table to multiple tables?

Also review the following blogs to optimize your database and table.
https://alexandrebrisebois.wordpress.com/2013/11/30/steps-towards-optimized-windows-azure-sql-databa...
https://alexandrebrisebois.wordpress.com/2014/01/26/indexes-are-crucial-on-windows-azure-sql-databas...


Thanks,
Lydia Zhang

Super Contributor
Posts: 1,026
Registered: ‎12-29-2015

Re: Azure Transport-Level Error

@v-yuezhe-msftimport. But I'm not even to the point of actually loading anything. I'm talking about the initial preview in the query editor. I can't even get that. I'll look into the links you provided. Unfortunately I can't really do much to optimize the table itself. This is a sql mirror of our Dynamics CRM 365. Specifically it's the Contact entity, which the company's customizations have added a bunch of extra fields to. I suppose we could make a secondary contact table that only has the fields I need for reporting, but if I could load the preview I could just query those fields anyway.

Moderator
Posts: 3,393
Registered: ‎03-10-2016

Re: Azure Transport-Level Error

Hi @KHorseman,

As you don't need all the fields in the table, could you please write a SQL statement to restrict number of columns and rows when loading data from this table ?

Thanks,
Lydia Zhang

Super Contributor
Posts: 1,026
Registered: ‎12-29-2015

Re: Azure Transport-Level Error

@v-yuezhe-msftin theory yes, except one reason I want to see a preview is to see what fields are available for me to use in that SQL query.

Moderator
Posts: 3,393
Registered: ‎03-10-2016

Re: Azure Transport-Level Error

Hi @KHorseman,

I would recommend you firstly connect to the Azure SQL Database in SQL Server Management Studio(SSMS), then select the large table to check your desired fields. After excuting an appropriate query to get data in SSMS, apply this query statement in Power BI Desktop.

Thanks,
Lydia Zhang