I am currently working on a migrating and older version of Microsoft Project server, Project Central 2000. The goal is to extract all the task with their assigned resources from the database. The data model seemed a bit confusing for me at first glance. I found this article really helpful although it does not list all the relevant tables in my case.
I want to get this information from the Project Central database:
- Project name
- Task name
- Completed Percentage
- Resource name
- Start date
- Finish date
- Is milestone
Joining these tables where proj_id is part of the join condition does the trick:
- msp_projects
- msp_tasks
- msp_assignments
- msp_resources
So, to obtain the task and resource name the following SQL can be used:
SELECT t.task_name, r.res_name
FROM msp_tasks t
INNER JOIN msp_assignments a on a.task_uid = t.task_uid and a.proj_id = t.proj_id
INNER JOIN msp_resources r on r.res_uid = a.res_uid and r.proj_id = a.proj_id
Ok great. Thanks for the information…