SQL to return an Outline Code Value
I would not recommend updating Project Server data directly in SQL. There is a lot of redundant data and calculated data stored in the database. Changing data may result in an inconsistent state (corruption) for your project plans.
Sometimes one needs to access Project Outline Code values using SQL. Below is an example of a stored procedure that returns a list of projects with the project name, the value of Enterprise Number 1, the value of Enterprise Text 1 and the value of Enterprise Project Outline Code 2.
The SELECT statement will reference five tables: one for the list of projects, one for the enterprise fields (non-outline) and three for the outline codes.
| Table | Purpose/Function |
|---|---|
| msp_web_projects | The list of projects published to Project Server. |
| msp_view_proj_projects_ent | Enterprise information for projects. |
| msp_outline_codes | Data about custom outline code definitions associated with a project. |
| msp_field_attributes | Data about field attributes such as custom WBS, custom field name aliases, and custom field formulae. |
| msp_attributes_strings |
Custom WBS code definition, alias, and formulae defined in msp_field_attributes. |
The first part of the stored procedure determines the Project Id for the latest copy of the Global Template. The Global Template is stored in the Project Server database as just like another project (other than the Project Type). Every time the Global Template is updated though, a new record is created and the old version remains.
The next part of the stored procedure sets the constant for the Enterprise Project Outline Code #2. For the complete list of constants for the Enterprise Fields, see this page on the Microsoft site.
The final part of the proc generates the recordset. Left joins are used in case one of the Enterprise fields has not been set.
CREATE PROCEDURE GetProjectList AS
BEGIN
-- Determine Project Id for latest Global Template
DECLARE @global_template_proj_id int
SELECT @global_template_proj_id =
(SELECT max(proj_id) FROM msp_projects WHERE proj_type = 2)
-- Define field code for Enterprise Project Outline Code 2
DECLARE @epoc2_field_code int
SELECT @epoc2_field_code = 188744590
-- Create list of projects
SELECT
projects.proj_name ,
ent.ProjectEnterpriseNumber1 ,
ent.ProjectEnterpriseText1 ,
epoc2.oc_cached_full_name AS ProjectEnterpriseOutlineCode2
FROM
msp_web_projects AS projects
-- Use left joins in case a project does not have a value
-- for the outline code.
LEFT JOIN msp_view_proj_projects_ent AS ent
ON ent.wproj_id = projects.wproj_id
LEFT JOIN msp_outline_codes AS epoc2
ON epoc2.code_uid = ent.ProjectEnterpriseOutlineCode2ID
AND epoc2.proj_id = @global_template_proj_id
AND epoc2.oc_field_id = @epoc2_field_code
LEFT JOIN msp_field_attributes AS epoc2a
ON epoc2a.proj_id = @global_template_proj_id
AND epoc2a.attrib_field_id = @epoc2_field_code
AND epoc2a.attrib_id = 206
AND epoc2a.attrib_value = -1
LEFT JOIN msp_attribute_strings AS epoc2s
ON epoc2s.proj_id = epoc2a.proj_id
AND epoc2s.as_id = epoc2a.as_id
AND epoc2s.as_position = 0
WHERE
projects.wproj_type = 2
RETURN
END
GO
If you are planning on diving into the SQL Server database for Project Server in order to create a custom report, make sure to set aside several hours. There is a lot of functionality in Project Server and an equal quantity of tables/fields to support it.
You can start by referring to the Microsoft Office Project 2003 Data Reference (PjDB.htm) and the Microsoft Office Project Server 2003 Data Reference (SvrDB.htm). These documents are on the Project Server installation CD. They are also part of the downloadable Project Server 2003 In-Box Documentation. Another good reference is the Field ID Values for Enterprise Custom Fields and Outline Codes.
