Process Reporting Database failing due to query timeout

Symptoms

When processing the reporting database the task fails and the logs show:-

'Error during process reporting database Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.'

Cause

In some circumstances out of date SQL statistics within the databases causes SQL to choose a sub optimum execution strategy leading to long query times. Or a combination of data volume and system load may cause query timeout periods to be exceeded.

Resolution

The first step is to update the statistics manually:

1. Open SQL Management Studio.
2. Open a new query window for the Discovery Center database
3. Enter and execute 'EXEC sp_updatestats'
4. Modify the first line of the SQL script below by replacing 'databaseNamePlaceholder' with your Database's name in single quotes.
   
   

   DECLARE @dbName NVARCHAR(MAX) = 'databaseNamePlaceholder'
   DECLARE @intDBID INT;
   SET @intDBID = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE name = @dbName);
   DBCC FLUSHPROCINDB (@intDBID);

   
   Execute the script.

If that does not resolve the problem follow the steps below:-

1. Launch SQL Management Studio and connect to the Analysis Services instance
2. Right click on the Analysis Services instance in the hierarchy view in Management Studio and select Properties.
3. Select the General property page and click on the Show Advanced (all) Properties checkbox
4. Review list of properties to find ExternalCommandTimeout
5. The default setting for this is normally 3600 seconds, increase this to double.