Sunday, July 22, 2012

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

I found many threads on MSDN forums asking for the solution on the following error message;
"OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00"
One of the similar thread on which I have answered is MSDN Thread, So I thought let us share some insights on the root cause and solution.

Whenever we process entire cube database, ssas engine process all the objects in parallel. It process dimensions first and then measure groups. So if your relational database tables contain huge volume of data and if the cube needs to fetch huge volume of data while processing then sometimes processing can exceed 1 hour (3600 sec) of time and then you will get the similar "Query timeout" error message. So for rectifying the issue you can increase the Query timeout from 3600 sec to 5400 sec (i.e. 1 hour 30 mins) or to any value as per your requirement. Now next question is, where should I change this property and the answer to this question is SSAS server property named "ExternalCommandTimeout". This server property is used to set the number of seconds that SSAS should wait to time out when issuing commands (queries) to external data sources.

Now you want to change the server property and for doing the same you needs to follow following steps;

1. Connect to SSAS services using SSMS and right click on instance name and select "Properties" option.


2. When you click on "Properties" option, you will get "Analysis Server Properties" window. Check the checkbox "Show Advanced(All) Properties" and go to the property named "ExternalCommandTimeout". You will find the default value as 3600 sec. Change the value as per your requirement and click OK button.
You do not need to restart services after changing the value of property.


After changing property, your cube processing will not fail till the value you have set for ExternalCommandTimeout property.

3 comments:

  1. Thanks Aniruddha for the explanation and help correcting the issue. How do I know when the query execution began? I see this message in the error logs every hour on the hour.

    ReplyDelete
  2. Facing "Connection timeout was exceeded" error when trying to refresh pivot connected to analysis server (tabular model)
    Please help me soon..very urgent!!

    ReplyDelete