Microsoft KB Archive/897769

= In Project Web Access for Project Server 2003, users receive task messages about unsubmitted time sheets or unsubmitted tasks for closed time periods =

Article ID: 897769

Article Last Modified on 7/27/2006

-

APPLIES TO


 * Microsoft Office Project Server 2003
 * Microsoft Office Project Server 2003, Service Pack 1 (SP1)

-





SYMPTOMS
When you log on to Microsoft Office Project Web Access 2003, you receive the following message in the Tasks section of your home page:

Your timesheet has not been submitted for the period.

The message includes unsubmitted tasks or unsubmitted timesheets for a time period that is closed.

Note The messages do not appear for time periods that are three months before the current date or older.



CAUSE
This problem occurs because Microsoft Office Project Server 2003 and Project Web Access 2003 do not let you modify the kind of task messages (alerts) that are displayed on the Project Web Access 2003 home page. Project Web Access 2003 uses the following stored procedures to obtain task message information:
 * MSP_WEB_SP_QRY_CountOfNewTasks
 * MSP_WEB_SP_QRY_CountOfOverdueStatusReports
 * MSP_WEB_SP_QRY_CountOfOverdueTasks
 * MSP_WEB_SP_QRY_CountOfPendingApprovals
 * MSP_WEB_SP_QRY_CountOfUnsubmittedTasks
 * MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets



WORKAROUND
To work around this problem, manually modify the Microsoft SQL Server stored procedures that Project Web Access 2003 uses to obtain task messages. The example scripts in this section support two variations, depending on what you want to filter. Depending on the lines that you comment out in the script, you can filter the task messages on many values. These include a static date and whether a time period is closed.

Warning Serious problems might occur if you manually modify the Project Server database. These problems might require that you reinstall Project Server or SQL Server. We cannot guarantee that you can solve problems that occur after you incorrectly modify the database. Manually modify the database at your own risk. We recommend that you perform a backup of the Project Server database before you follow these steps.

To modify the stored procedures that Project Web Access 2003 uses, follow these steps on the computer that is running SQL Server:
 * 1) Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
 * 2) On the Query menu, click Change Database.
 * 3) Click the Project Server database, and then click OK.
 * 4) Copy the appropriate Unsubmitted Tasks script that is provided later in this article. Then, paste the script into the Query window. The Project Server 2003 original release version of the script and the Microsoft Project Server 2003 Service Pack 1 (SP1) version of the script are provided later in this article.
 * 5) On the Query menu, click Execute. After the script is completed, click Clear Window on the Edit menu.
 * 6) Copy the appropriate Unsubmitted Time Sheets script that is provided later in this article. Then, paste the script into the Query window. The Project Server 2003 original release version of the script and the Project Server 2003 SP1 version of the script are provided later in this article.
 * 7) On the Query menu, click Execute.

Use one of the following scripts, depending on the level of Project Server 2003 that you are running. For example, if you are running Project Server 2003 without a service pack, use the following scripts:
 * Unsubmitted Tasks on the original release version of Project Server 2003
 * Unsubmitted Time Sheets on the original release version of Project Server 2003

The scripts that are provided in this article specifically configure the stored procedures for unsubmitted tasks and for unsubmitted time sheets. You can modify these scripts to configure other stored procedure for other types of task messages. To modify the scripts, replace &quot;MSP_WEB_SP_QRY_CountOfUnsubmittedTasks&quot; and &quot;MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets&quot; in the scripts with the stored procedures for the other task messages that you want to configure. The stored procedures are listed in the &quot;Cause&quot; section.

Unsubmitted Tasks on the original release version of Project Server 2003
--****************************** -- This Script modifies the existing stored procedure -- for unsubmitted tasks on Project Server 2003 RTM --****************************** if exists(select * from sysobjects where name = 'MSP_WEB_SP_QRY_CountOfUnsubmittedTasks') drop procedure dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks go CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks @p0 INT, @p1 DATETIME AS SELECT DISTINCT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON (WTP.WPRD_START_DATE WA.ASSN_START_DATE AND WTP.WPRD_ID>=100) INNER JOIN MSP_WEB_PROJECTS WP ON (WA.WPROJ_ID = WP.WPROJ_ID) WHERE WA.WRES_ID =@p0 AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE AND WAA.WAPPROVAL_STATUS <> 2) AND WA.TASK_IS_SUMMARY = 0 AND WP.WPROJ_TYPE <> 3 AND WA.ASSN_BOOKING_TYPE = 0 AND WA.WASSN_REMOVED_BY_RESOURCE=0 AND (WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1) AND WA.WASSN_DELETED_IN_PROJ = 0 --******************************* --the first option filters out alerts the fall before the date specified --the second option filters out alerts that fall with in a closed time period AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000' AND WTP.WPRD_STATE = 0 --*******************************

ORDER BY WTP.WPRD_START_DATE

RETURN go grant execute on MSP_WEB_SP_QRY_CountOfUnsubmittedTasks to MSProjectServerRole go

Unsubmitted Tasks on Project Server 2003 SP1
--****************************** -- This Script modifies the existing stored procedure -- for unsubmitted tasks on Project Server 2003 SP1 --****************************** CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks @p0 INT, @p1 DATETIME AS SELECT DISTINCT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON (WTP.WPRD_START_DATE< WA.ASSN_FINISH_DATE AND WTP.WPRD_FINISH_DATE> WA.ASSN_START_DATE AND WTP.WPRD_ID>=100) INNER JOIN MSP_WEB_PROJECTS WP ON (WA.WPROJ_ID = WP.WPROJ_ID) WHERE WA.WRES_ID =@p0 AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE AND WAA.WAPPROVAL_STATUS <> 2) AND WA.TASK_IS_SUMMARY = 0 AND WP.WPROJ_TYPE <> 3 AND WA.ASSN_BOOKING_TYPE = 0 AND WA.WASSN_REMOVED_BY_RESOURCE=0 AND (WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1) AND WA.WASSN_DELETED_IN_PROJ = 0 --******************************* --the first option filters out alerts the fall before the date specified --the second option filters out alerts that fall with in a closed time period AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000' AND WTP.WPRD_STATE = 0 --******************************* ORDER BY WTP.WPRD_START_DATE RETURN GO GRANT EXECUTE ON dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTasks TO MSProjectServerRole GO

Unsubmitted Time Sheets on the original release version of Project Server 2003
--****************************** -- This Script modifies the existing stored procedure -- for unsubmitted time sheets on Project Server 2003 RTM --****************************** if exists(select * from sysobjects where name = 'MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets') drop procedure dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets go CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets @p0 INT, @p1 DATETIME AS SELECT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE, COUNT (DISTINCT WA.WRES_ID) AS TCOUNT FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON (WTP.WPRD_ID>=100 AND ((WTP.WPRD_START_DATE< WA.ASSN_FINISH_DATE AND WTP.WPRD_FINISH_DATE> WA.ASSN_START_DATE) OR (((WA.WASSN_ACTUALS_PENDING <> 0) OR (WA.WASSN_PCT_COMP< 100 AND WASSN_PCT_COMP>0)) AND WA.ASSN_START_DATE<= WTP.WPRD_FINISH_DATE))) INNER JOIN MSP_WEB_PROJECTS ON (WA.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID)

WHERE (WA.WASSN_IS_CONFIRMED<>0 OR MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT<>0) AND WA.WASSN_REMOVED_BY_RESOURCE=0 AND MSP_WEB_PROJECTS.WPROJ_TYPE <> 3 AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE AND WA.WRES_ID in ( -- Permission 212: Approve timesheet permission select WRES_ID from MSP_WEB_FN_SEC_GetAllResourcesResCanViewByViewID(@p0, 212, -1) ) AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE ) --******************************* --the first option filters out alerts the fall before the date specified --the second option filters out alerts that fall with in a closed time period AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000' AND WTP.WPRD_STATE = 0 --******************************* GROUP BY WTP.WPRD_START_DATE,WTP.WPRD_FINISH_DATE ORDER BY WTP.WPRD_START_DATE RETURN go grant execute on MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets to MSProjectServerRole go

Unsubmitted Time Sheets on Project Server 2003 SP1
--****************************** -- This Script modifies the existing stored procedure -- for unsubmitted time sheets on Project Server 2003 SP1 --****************************** CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets @p0 INT, @p1 DATETIME AS SELECT WTP.WPRD_START_DATE, WTP.WPRD_FINISH_DATE, COUNT (DISTINCT WA.WRES_ID) AS TCOUNT FROM MSP_WEB_TIME_PERIODS WTP INNER JOIN MSP_WEB_ASSIGNMENTS WA ON (WTP.WPRD_ID>=100 AND WTP.WPRD_START_DATEWA.ASSN_START_DATE) INNER JOIN MSP_WEB_PROJECTS ON (WA.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID) WHERE (WA.WASSN_IS_CONFIRMED<>0 OR MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT<>0) AND (WA.WASSN_IS_CONFIRMED<>0 or MSP_WEB_PROJECTS.WPROJ_ADMINPROJECT = 1) AND WA.WASSN_DELETED_IN_PROJ = 0 AND WA.TASK_IS_SUMMARY = 0 AND (WA.WASSN_REMOVED_BY_RESOURCE=0 OR WA.WASSN_LOCKDOWN_BY_MANAGER<>0) AND WA.ASSN_BOOKING_TYPE = 0 AND MSP_WEB_PROJECTS.WPROJ_TYPE <> 3 AND DATEADD (mm,3,WTP.WPRD_FINISH_DATE)>=@p1 AND @p1>WTP.WPRD_FINISH_DATE AND WA.WRES_ID in ( -- Permission 212: Approve timesheet permission select WRES_ID from MSP_WEB_FN_SEC_GetAllResourcesResCanViewByViewID(@p0, 212, -1) ) AND NOT EXISTS (SELECT * FROM MSP_WEB_WORK_APPROVAL WAA WHERE WAA.WRES_ID=WA.WRES_ID AND WAA.WPRD_START_DATE = WTP.WPRD_START_DATE AND WAA.WPRD_FINISH_DATE = WTP.WPRD_FINISH_DATE) --******************************* --the first option filters out alerts the fall before the date specified --the second option filters out alerts that fall with in a closed time period AND WTP.WPRD_FINISH_DATE > '2004-12-01 00:00:00.000' AND WTP.WPRD_STATE = 0 --******************************* GROUP BY WTP.WPRD_START_DATE,WTP.WPRD_FINISH_DATE ORDER BY WTP.WPRD_START_DATE RETURN GO GRANT EXECUTE ON dbo.MSP_WEB_SP_QRY_CountOfUnsubmittedTimesheets TO MSProjectServerRole GO



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Additional query words: alerts

Keywords: kbstoredproc kbdbase kbtshoot kbdatabase kbuser kbsyssettings KB897769

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.