EPM Central Project Server Tutorials
Home Deployment    MS Project PWA SharePoint Articles Administrators FAQs Consulting Guest Book 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

  VBA

 
 
Administration -> MS Project Server 2003 -> Enterprise Fields
Sample Queries


SQL query to retrieve Project Outline Code1 Value for all projects

The following query displays Enterprise Project Outline Code1 value for all the projects. Please refer lookup table and set the appropriate value for variable "@EntProjOutlineCode" in the following script

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database. Set the appropriate enterprise project outline code value

DECLARE @EntProjOutlineCode As int

SET @EntProjOutlineCode = 188744589

SELECT DISTINCT dbo.MSP_PROJECTS.PROJ_NAME, CONVERT(NVARCHAR(100),
dbo.MSP_OUTLINE_CODES.OC_NAME) AS PROJOUTLINECODE
FROM dbo.MSP_CODE_FIELDS INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_CODE_FIELDS.PROJ_ID = dbo.MSP_PROJECTS.PROJ_ID LEFT OUTER JOIN
dbo.MSP_OUTLINE_CODES ON dbo.MSP_CODE_FIELDS.CODE_UID = dbo.MSP_OUTLINE_CODES.CODE_UID
WHERE (dbo.MSP_CODE_FIELDS.CODE_FIELD_ID = @EntProjOutlineCode)
ORDER BY dbo.MSP_PROJECTS.PROJ_NAME



SQL query to retrieve Enterprise Project Text1 Value for all projects

The following query displays Enterprise Project Text1 value for all the projects. Please refer lookup table and set the appropriate value for variable "@EntProjTxtID" in the following script

How to Run: Run the following query on SQL Query Analyzer, by selecting Project Server database. Set the appropriate enterprise project text value

Declare @EntProjTxtID as Int

SET @EntProjTxtID = 188744729

SELECT dbo.MSP_PROJECTS.PROJ_ID,dbo.MSP_PROJECTS .PROJ_NAME,
dbo.MSP_TEXT_FIELDS.TEXT_VALUE AS EntProjTxtValue
FROM dbo.MSP_WEB_PROJECTS INNER JOIN
dbo.MSP_PROJECTS ON dbo.MSP_WEB_PROJECTS.PROJ_ID = dbo.MSP_PROJECTS.PROJ_ID LEFT OUTER JOIN
dbo.MSP_TEXT_FIELDS ON dbo.MSP_PROJECTS .PROJ_ID = dbo.MSP_TEXT_FIELDS .PROJ_ID AND
dbo.MSP_TEXT_FIELDS .TEXT_CATEGORY = 0 AND
dbo.MSP_TEXT_FIELDS.TEXT_FIELD_ID = @EntProjTxtID AND
dbo.MSP_TEXT_FIELDS.TEXT_REF_UID = 0 AND dbo.MSP_TEXT_FIELDS.TEXT_VALUE IS NOT NULL AND
dbo.MSP_TEXT_FIELDS .TEXT_VALUE <> '' AND dbo.MSP_TEXT_FIELDS .TEXT_VALUE <> ' '




Copyright 2013. EPM Central. All rights reserved.