cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User
Super User

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 v-yuezhe-msft
Moderator

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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 v-yuezhe-msft
Moderator

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

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 v-yuezhe-msft
Moderator

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DipteshPatel Frequent Visitor
Frequent Visitor

Power Bi scheduled data refresh failed ....

I'm having an issue with a Dataset failing to refresh and it would appear to be to do with the fact my model is blending data from two separate databases in two separate Azure servers. I've created a report in PBI Desktop that connects to our separate DEV and PROD Azure Servers with no issues - Desktop lets me blend the data without a complaint.

 

I set up a schedule for refresh, but it always fails and The Refresh history messages shows a seemingly random table each time as being the one to trip it up.

 

 

If Power BI Desktop is letting me blend these sources in to the same report, what is it about the Service that is preventing it?

 

Is it a bug in the Service, or intentional for some reason? Is there a suggested workaround?

 

 

Thanks in advance.