D
Don
I am posting this because it may help others resolve the same issues we
were having.
Were started having a problem with the OLAP cube build and received the
following errors in the App Log. We were provided with the following
solution from Microsoft. The main problem was some of the cube tables
had more than 10 million rows.
PROJECT WEB ACCESS ERROR
__________________________________________________________________________
The cube scheduled to be built on 6/20/2006 at 8:44 AM failed.
(-2147217871) Timeout expired
==================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
==================================================================
LOG ERRORS
__________________________________________________________________________
Component: Views Notification Service
File:
C:\Office\devsp\project\pcportal\source\viewsnotify\ViewNotify.cpp
Line: 127
Error Number: 0x800a0005
Description: <Description><![CDATA[View scheduler received error while
scheduling views]]></Description>
___________________________________
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x5
Description: <Description><![CDATA[Error Information: Invalid procedure
call or argument
Additional Information: An error occured processing the following view
:-
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
==================================================================
Component: Views Notification Service
File:
C:\Office\devsp\project\pcportal\source\viewsnotify\ViewNotify.cpp
Line: 132
Error Number: 0x800a0005
Description: <Description><![CDATA[The views processor failed to
process the following document:
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
==================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x5
Description: <Description><![CDATA[Error Information: Invalid procedure
call or argument
Additional Information: An error occured processing the following view
:-
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
=================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Description: <Description><![CDATA[Error Information:
Additional Information: An error occured calling
CallViewGenerator(projectserver, -1, <ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>)]]></Description>
=================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x80004005
Description: <Description><![CDATA[Failed to generate OLAP Cube for the
following Request :
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>]]></Description>
================================================================
Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: 1083
Description: <Description><![CDATA[Error #: -2147217871 Desc. :Timeout
expired]]></Description>
================================================================
Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: -2147217871
Description: <Description><![CDATA[Error populating the staging tables.
Error: 'Timeout expired']]></Description>
================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================
MICROSOFT SOLUTION
_________________________________________________________________________
Please create a backup of you ProjectServer database before attempting
this.
TRUNCATE TABLE MSP_CUBE_ASSN_FACT
TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
This should allow the cube to build properly, without timeout failure
Since this issue will probably re-occur in the future, you can schedule
the truncation of these tables as follows:
On the computer that is running SQL Server, start SQL Enterprise
Manager.
1. On the computer that is running SQL Server, start SQL Enterprise
Manager.
Expand Microsoft SQL Servers, expand SQL Server Group, expand (
Your_SQL_Server ) (Windows NT).
Note If you have not named the server that is running SQL Server, the
default name of the server will be "(local)".
2. Expand Management, and then expand SQL Server Agent.
3. Right-click Jobs, and then click New Job.
4. Click the General tab, and then type a name for this job. For
example, type Table_Cleanup .
5. Click to select the Enabled check box, and then click an owner for
this job in the Owner list. Leave the default values in the other
boxes. You may type a description for this job in the Description box.
6. Click the Steps tab, and then click New.
7. In the Step Name box, type a name for the step. For example, type
Step 1 .
8. In the Type box, click Transact-SQL Script (TSQL), and then click
ProjectServer in the Database list.
9. In the Command box, type the following commands. Press ENTER after
each command.
TRUNCATE TABLE MSP_CUBE_ASSN_FACT
TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
10. Click OK.
11. Click the Schedules tab, and then click New Schedule.
12. In the Name box, type a name for the schedule. For example, type
Cleanup_Schedule .
13. Click to select the Enabled check box.
14. Under Schedule, configure an appropriate schedule to run this job.
Run the job for about 45 minutes before you build the OLAP cube.
15. Click OK, and then click the Notifications tab.
16.
Select an appropriate method to notify the operator when the job is
completed.
17. Click OK.
18. Quit SQL Enterprise Manager.
were having.
Were started having a problem with the OLAP cube build and received the
following errors in the App Log. We were provided with the following
solution from Microsoft. The main problem was some of the cube tables
had more than 10 million rows.
PROJECT WEB ACCESS ERROR
__________________________________________________________________________
The cube scheduled to be built on 6/20/2006 at 8:44 AM failed.
(-2147217871) Timeout expired
==================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
==================================================================
LOG ERRORS
__________________________________________________________________________
Component: Views Notification Service
File:
C:\Office\devsp\project\pcportal\source\viewsnotify\ViewNotify.cpp
Line: 127
Error Number: 0x800a0005
Description: <Description><![CDATA[View scheduler received error while
scheduling views]]></Description>
___________________________________
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x5
Description: <Description><![CDATA[Error Information: Invalid procedure
call or argument
Additional Information: An error occured processing the following view
:-
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
==================================================================
Component: Views Notification Service
File:
C:\Office\devsp\project\pcportal\source\viewsnotify\ViewNotify.cpp
Line: 132
Error Number: 0x800a0005
Description: <Description><![CDATA[The views processor failed to
process the following document:
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
==================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x5
Description: <Description><![CDATA[Error Information: Invalid procedure
call or argument
Additional Information: An error occured processing the following view
:-
<ViewsDropData>
<ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><Connection>Provider=SQLOLEDB;
Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</Connection><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>
<OrgGuid>projectserver</OrgGuid>
<BasePath>projectserver</BasePath>
<TimeStamp>2006-06-20T21:12:39</TimeStamp>
</ViewsDropData>]]></Description>
=================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Description: <Description><![CDATA[Error Information:
Additional Information: An error occured calling
CallViewGenerator(projectserver, -1, <ProjectData>
<EntProjectName><![CDATA[resglobal] ]></EntProjectName>
<EntResourceIDList>*</EntResourceIDList>
<EntResourceStartDate>2005-01-01T00:00:00</EntResourceStartDate>
<EntResourceFinishDate>2010-12-31T00:00:00</EntResourceFinishDate>
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>
</ProjectData>)]]></Description>
=================================================================
Component: PCSViews
File: ViewsDrop.cls
Line: -1
Error Number: 0x80004005
Description: <Description><![CDATA[Failed to generate OLAP Cube for the
following Request :
<BuildCubeRequest><CubeUpdate><CubeStart>20050101</CubeStart><CubeFinish>20101231</CubeFinish><DBType>0</DBType><DSOConnection>Provider=SQLOLEDB.1;Persist
Security Info=False;Initial Catalog=MSP_PROD;Data
Source=SQLPS0030,3180</DSOConnection><Server>roc08wna103</Server><CubeDB>msepmprod</CubeDB><CubeDesc>msepmprod</CubeDesc><BasePath>projectserver</BasePath><CubeGUID>149FFC0F-B0F4-4344-8E61-C5EFFE8B211C</CubeGUID></CubeUpdate></BuildCubeRequest>]]></Description>
================================================================
Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: 1083
Description: <Description><![CDATA[Error #: -2147217871 Desc. :Timeout
expired]]></Description>
================================================================
Component: MSP Resource Availablity Refresh and OLAP Cube Creation
Component (ProjOLAP)
File: PROJOLAPProcess
Line: -2147217871
Description: <Description><![CDATA[Error populating the staging tables.
Error: 'Timeout expired']]></Description>
================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================
MICROSOFT SOLUTION
_________________________________________________________________________
Please create a backup of you ProjectServer database before attempting
this.
TRUNCATE TABLE MSP_CUBE_ASSN_FACT
TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
This should allow the cube to build properly, without timeout failure
Since this issue will probably re-occur in the future, you can schedule
the truncation of these tables as follows:
On the computer that is running SQL Server, start SQL Enterprise
Manager.
1. On the computer that is running SQL Server, start SQL Enterprise
Manager.
Expand Microsoft SQL Servers, expand SQL Server Group, expand (
Your_SQL_Server ) (Windows NT).
Note If you have not named the server that is running SQL Server, the
default name of the server will be "(local)".
2. Expand Management, and then expand SQL Server Agent.
3. Right-click Jobs, and then click New Job.
4. Click the General tab, and then type a name for this job. For
example, type Table_Cleanup .
5. Click to select the Enabled check box, and then click an owner for
this job in the Owner list. Leave the default values in the other
boxes. You may type a description for this job in the Description box.
6. Click the Steps tab, and then click New.
7. In the Step Name box, type a name for the step. For example, type
Step 1 .
8. In the Type box, click Transact-SQL Script (TSQL), and then click
ProjectServer in the Database list.
9. In the Command box, type the following commands. Press ENTER after
each command.
TRUNCATE TABLE MSP_CUBE_ASSN_FACT
TRUNCATE TABLE MSP_CUBE_RES_AVAIL_FACT
TRUNCATE TABLE MSP_CUBE_TIME_BY_DAY
10. Click OK.
11. Click the Schedules tab, and then click New Schedule.
12. In the Name box, type a name for the schedule. For example, type
Cleanup_Schedule .
13. Click to select the Enabled check box.
14. Under Schedule, configure an appropriate schedule to run this job.
Run the job for about 45 minutes before you build the OLAP cube.
15. Click OK, and then click the Notifications tab.
16.
Select an appropriate method to notify the operator when the job is
completed.
17. Click OK.
18. Quit SQL Enterprise Manager.