Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Anonymous,

      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-databases/
      https://alexandrebrisebois.wordpress.com/2014/01/26/indexes-are-crucial-on-windows-azure-sql-database/


      Thanks,
      Lydia Zhang

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Anonymousimport. 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.