|
|
|
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 <> ' '
|
| |
|
