Processing OLAP Cube Fails from within SQL Server Agent Job

2
votes

I have this SQL Server Agent job. There's a step in the job for processing an OLAP cube on a remote server. The step type is Analysis Services Command. Below is the command:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>DatabaseName</DatabaseID>
  </Object>
  <Type>ProcessFull</Type>
</Process>

When processing the step, it fails after 40 minutes with the below error message:

Executed as user: DOMAIN\user. Microsoft.AnalysisServices.Xmla.ConnectionException: The connection either timed out or was lost.

Now I granted this user Administrator Permissions on the remote Analysis Services server. I can connect to this server from within the Management Studio and process the database successfully.

But for some reason the job can't seem to process the cube.

Help?

Thanks.

2
sql-serverprocessssasjobscube

2 Answers

0
votes

It sounds like the ExternalCommandTimeout property may need to be increased. If that's the case, connect to the analysis services server instance in Management Studio. Right click on the server and choose Properties. Select "Show Advanced (All) Properties" and increase the value of the "ExternalCommandTimeout".

0
votes

40 minutes seems to much to be a timeout on SSAS.

Does your DSV connects to more that one data source? It may connect to the first and fails on the second. Or it may be a deadlock on your sql server. Did you run sp_who2 on the server to see the status of the process?