Query to Delete Specific Tasks of a plan for Specific Resources

I’ve seen situations where tasks deleted from project plan still shows up in users’ PWA timesheet. No matter how many times Project Manager republishes the project plan in MS Project Professional; those tasks will stay there in users’ timesheet. In perfect MS Project world (in reality nothing is perfect in this universe 🙂 ) those tasks should have ‘X’ in indicator column to show they are staled tasks.

Sometimes marking a task as 100% complete in MS Project Professional & republishing the project plan doesn’t mark it as complete in PWA timesheet.

In some of these scenarios, users consider the task as active & may charge their time for it. On getting the task update, Project Manager will receive the error when he tries to do task update

A user can select the task & click Hide button to remove it from timesheet view. But you might want to remove such task assignment from users’ PWA timesheet. Think about if you have one such plan with hundreds of tasks & lots of resources.

You can use the query below in any such scenario to remove particular task assignments from database.


— View list of tasks for a resource in a particular project before deleting

select ma.wassn_id, mp.proj_name, ma.task_name, mr.res_name from msp_web_assignments ma
join msp_web_projects mp
on ma.wproj_id = mp.wproj_id
join msp_web_resources mr
on ma.wres_id = mr.wres_id
where
Proj_name like ‘ProjectPlanName
AND
Res_name like ‘ResourceName%’
AND
Task_Name like ‘Task_Name

— Delete particular tasks for a resource in a particular project from their PWA Timesheets.
/*
delete from msp_web_assignments
where wassn_id in (
select ma.wassn_id from msp_web_assignments ma
join msp_web_projects mp
on ma.wproj_id = mp.wproj_id
join msp_web_resources mr
on ma.wres_id = mr.wres_id
where
Proj_name like ‘ProjectPlanName
AND
Res_name like ‘ResourceName
AND
Task_Name like ‘Task_Name
)
*/

(I don’t guarantee working of this method any hence the issues which may arise by following this article. Test this process first in development. Contact your DBA & Project Server Administrator before running queries. )

Leave a Reply

Enter the CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.