Query to Delete Specific Tasks of a plan for Specific Resources

Share on facebook
Share on twitter
Share on linkedin

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

Sign up for Softvative Newsletter

Receive the latest technology and leadership news and resources from us

Subscribe To Softvative Newsletter

Get notified about new articles

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest