How to extend the timeout for long running queries


  • I unfortunately have a query that needs to run for 2 hours (long story) to load a year's worth of data into PowerPivot (RTM).  PowerPivot stops with an error after running for 1 hour.  I tried changing the "General Timeout" setting from 0 to 28800 (8 hours) in the SQL conneciton settings (native client 10.0) and I also managed to get the server owner to increase the "Query Timeout" setting in the SQL Server's Connections dialog but PowerPivot still errors out after 1 hr.  

    A workaround will be to use SSIS to run the query and store the result in another database but I'd rather not do that if I don't have to.

    Error Message

    The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source


    More Details:

    OLE DB or ODBC error: Query timeout expired; HYT00.

    An error occurred while processing the 'XXXX' table.

    The operation has been cancelled.

    Is there any other place where the timeout can be set or controlled that I've missed?



    Saturday, December 18, 2010 1:24 AM


All replies

  • Try 

    1) Setting "ExternalCommandTimeout" in the server configuration file (msmdsrv.ini) to a multiple of seconds you want (in this case >7200, by default it is 3600(1hr)).

    2) Restart the server.

    3) Refresh the data.


    Please let me know, if this doesn't work.

    Wednesday, February 09, 2011 8:35 PM
  • Is there a separate timeout setting for the table preview when you click on Edit table? I'm getting a time out error there but not for refreshing the table.
    Thursday, October 31, 2013 2:00 PM
  • Where can see the file server configuration file (msmdsrv.ini) ? I have a similar issue with power pivot that is deployed in sharepoint 2010
    Monday, March 03, 2014 7:48 PM