Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Scheduled Refresh Fails Intermittantly

We have an issue with scheduled refreshes not working occasionally.  This is ocurring with two different on-premise data gateways that connect to two different SQL Servers and databases which are part of an Availability Group.  The error is basically the same each time.  The Table value changes to one of the queries we have in the dataset file.  The Pre-Login and Handshake values change as well sometimes.   

 

Part of Error message:

Underlying error code: -2147467259 Table: "TableName".  
Underlying error message: Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=133; handshake=14961;

We have added connection timeout of 10 minutes to each of the queries in the dataset file. but the issue persists.

 

This article below seems close to what we are encountering but is not in context of PowerBI. 

https://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes

 

I have scanned the web and this forum, but I have not found a fix. Has anyone encountered the same issue?   

Need Help

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi, can you please try adding a timeout like 120 minutes for each query in edit queries. That is the power bi refresh limit from service. If that much doesn't work then you have a problem because 10 minutes is not so much for big tables. The engine could be busy at that time and work upload the data under 10 minutes.

     

    I don't think is a power bi problem it looks like an engine problem getting the query results very slow.

     

    Regards,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the suggestion.   The failure error occurs within 20-30 seconds so the 10 minute timeout should suffice I would think. 

      • ibarrau's avatar
        ibarrau
        Super User
        6 years ago

        If the error is in 20-30 seconds is a connection problem. The gateway is having trouble to connect to the data source. Please be sure you have the gateway output ports opened and to test a connection from the gateway administration and from the gateway computer to garantee you can actually connect the data.

         

        Regards,

  • venal's avatar
    venal
    Memorable Member
    6 years ago

    Anonymous 

    Is the gateway is latest version. (please try to open the Gateway and sign-in with the right credentials)

    If it is the latest Gateway version, please check the dataset settings with Gateway Connection and Data Source Credentials.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more

    If this post was helpful may I ask you to mark it as solution and give it some kudos?