query PS database to identify where task duration <> resource dura

[Jesse]

initially posted on the Server thread....may be better here

I use MS Project 2003 and PS 2003. I often find tasks in my schedule where
the task duration is longer than the duration for the resource assigned to
that task. I periodically go through my projects to identify and resolve this
situation.

What I'd like is to query the PS SQL database to locate tasks in my projects
where teh task duration differs from the resource start/duration/finish.
Ultimately I'd like to be able to run this query against any project or all
projects in the PS database. Can someone help me with a query

 

[Rod Gill]

The only time I can think this will happen is when there are two or more
assignments on the same task. In that case having one resource finish early
is quite valid, so why change it? It would be highly dangerous to do a
global update like that, you could really annoy a number of PMs!

It is possible to directly edit the project tables, but for projects already
published you need to open them and re-publish otherwise you will create
discrepancies.

 

[Jesse]

Thanks Rod, I'm not looking to update the tasks with any kind of global
update. I'm looking to identify such tasks to I can focus on project
tasks/assignments to research.
--
Thanks, Jesse

The only time I can think this will happen is when there are two or more
assignments on the same task. In that case having one resource finish early
is quite valid, so why change it? It would be highly dangerous to do a
global update like that, you could really annoy a number of PMs!

It is possible to directly edit the project tables, but for projects already
published you need to open them and re-publish otherwise you will create
discrepancies.

 

[Rod Gill]

Use the pjdb.htm file in one of Project's program folders. It describes the
table structure. You can create a query comparing assignment start/finish
with Task start finish. I would view this in Excel.


--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more

Thanks Rod, I'm not looking to update the tasks with any kind of global
update. I'm looking to identify such tasks to I can focus on project
tasks/assignments to research.

 

[Jesse]

Thanks Rod, I found the pjsvrdb.htm file. I'm using Project Server 2003.
Should I compare start/finish from MSP_WEB_Assignments with start/finish from
MSP_Tasks? I'm new to looking at the SQL database but very excited and
motivated to learn it.

 

[Rod Gill]

The WEB tables only hold published data, so the straight MSP_ tables are
more likely to be accurate.

 

[Jesse]

Thanks Rod, that's good to know

 

[Jesse]

Here's what I came up with....seems to work well.

select t.proj_id, t.task_id, t.task_start_date, a.assn_start_date,
t.task_finish_date, a.assn_finish_date
from dbo.MSP_ASSIGNMENTS a, dbo.MSP_TASKS t
where t.proj_id = a.proj_id
and t.task_uid = a.task_uid
and t.proj_id = '819'
and (t.task_start_date <> a.assn_start_date or t.task_finish_date <>
a.assn_finish_date)
order by t.task_id