EPM Central Project Server Tutorials
Home Deployment    MS Project PWA SharePoint Articles Administrators FAQs Consulting Search Site Map
  Error Messages

Project Server 2003

PWA Login DropDown

Inactive vs. Delete

Actual Work Protected

  2003 SQL Queries

  Enterprise Fields
    Project Fields
    Resource Fields
    Sample Queries

Admin Tools

Re-associate Site

Project Archival


Administration -> MS Project Server 2003 -> SQL Queries
Local Resources in Project Plans

The following query displays the list of the local resource names by project. Also it displays “work, actual work and most recent assignment finish date” for each local resource.

Target Audience: Project Server Administrator/ Report Developer

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database.


Create table #Local_Resources (res_name varchar(255))
insert into #Local_Resources
select r.res_name
from dbo.msp_resources r
where r.proj_id = 1

-- Select info on invalid resources

select r.proj_id As Proj_ID,
p.proj_name as Proj_Name,
r.res_name As Res_Name,
max(a.assn_finish_date) as Work_Till,
SUM(a.assn_work / 60000) AS Work,
SUM(a.assn_act_work / 60000) AS Actual_Work
from dbo.MSP_RESOURCES r
inner join dbo.MSP_PROJECTS p on
r.proj_id = p.proj_id
left outer join #Local_Resources vr on
r.res_name = vr.res_name
left outer join dbo.MSP_ASSIGNMENTS a on
r.proj_id = a.proj_id AND
r.res_uid = a.res_uid

where (r.res_uid >0) and
(vr.res_name is null)
group by p.proj_name,
order by p.proj_name, r.res_name

-- Drop Temp Tables
drop table #Local_Resources

Sample Output:

Copyright © 2013. EPM Central. All rights reserved.