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

Project Server 2007

Databases

Extend Workspace

Delete Workspace

Fiscal Periods

Administrative Categories

Timesheet Periods

Lock Timesheets

Queue Administration

2007 SQL Queries

Video - PWA Permissions

Video - Manage Groups

Video - Manage Categories

Video - RBS

 
 
Administration -> MS Project Server 2007 -> SQL Queries -> Resource Names used in the Project


1. Local Resources used in the Project Plan:

The following query displays the list of local resources used in enterprise projects.

How to Run: Run the following SQL query by selecting Published database.

SQL Query:

SELECT DISTINCT MSP_PROJECT_RESOURCES.PROJ_UID,
MSP_PROJECTS.PROJ_NAME,
MSP_PROJECT_RESOURCES.RES_NAME
FROM MSP_PROJECT_RESOURCES INNER JOIN
MSP_PROJECTS ON MSP_PROJECT_RESOURCES.PROJ_UID = MSP_PROJECTS.PROJ_UID
WHERE MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 0 AND
MSP_PROJECT_RESOURCES.RES_NAME IS NOT NULL







2. Resources used in the Project Plan

The following query displays the list of resource names (local and enterprise) appears in the enterprise projects by project name. This query lists all the names you see in resource sheet view of the project plan.

How to Run: Run the following SQL query by selecting Published database.

SQL Query:

SELECT DISTINCT MSP_PROJECT_RESOURCES.PROJ_UID,
MSP_PROJECTS.PROJ_NAME,
MSP_PROJECT_RESOURCES.RES_NAME,
CASE
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 2 AND
MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 0
THEN 'Work Resource - Local'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 2 AND
MSP_PROJECT_RESOURCES.RES_IS_ENTERPRISE_RESOURCE = 1 THEN 'Work Resource - Enterprise'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 20 THEN 'Generic Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 21 THEN 'Material Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 22 THEN 'Generic Material Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 25 THEN 'Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 26 THEN 'Generic Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 50 THEN 'Budget Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 51 THEN 'Budget Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 52 THEN 'Budget Material Resource' ,
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 53 THEN 'Generic Budget Work Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 54 THEN 'Generic Budget Cost Resource'
WHEN MSP_PROJECT_RESOURCES.RES_TYPE = 55 THEN 'Generic Budget Material Resource'
END AS RES_DESC
FROM MSP_PROJECT_RESOURCES INNER JOIN
MSP_PROJECTS ON MSP_PROJECT_RESOURCES.PROJ_UID = MSP_PROJECTS.PROJ_UID
WHERE MSP_PROJECT_RESOURCES.RES_NAME IS NOT NULL







Copyright 2013. EPM Central. All rights reserved.