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

The following query lists all issues from WSS Sites (This query based on default workspace template "Project Workspace". If you already customized issues, please you have to extend the following query by including the additional columns.)

Default columns for Issue list:

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

SELECT dbo.Webs.Title AS Site_Name, ProjectServer.dbo.MSP_WEB_PROJECTS.WPROJ_ID, dbo.UserData.tp_ID AS IssueID,
dbo.UserData.tp_Version AS Version, dbo.UserData.nvarchar3 AS Title,
UserInfo_1.tp_Title AS AssignedTo,
dbo.UserData.nvarchar1 AS Status,
dbo.UserData.nvarchar2 AS Category,
UserInfo_2.tp_Title AS Owner,
dbo.UserData.nvarchar4 AS Priority,
dbo.UserData.datetime1 AS Due_Date,
CAST(dbo.UserData.ntext3 AS VARCHAR(7200)) AS Discussions,
CAST(dbo.UserData.ntext4 AS VARCHAR(7200)) AS Resolution,
dbo.Sites.FullUrl AS FullURL
dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId INNER JOIN
dbo.Sites ON dbo.UserData.tp_SiteId = dbo.Sites.Id INNER JOIN
dbo.Webs ON dbo.Sites.Id = dbo.Webs.SiteId INNER JOIN
dbo.UserInfo UserInfo_1 ON dbo.UserData.tp_SiteId = UserInfo_1.tp_SiteID AND
dbo.UserData.int3 = UserInfo_1.tp_ID LEFT OUTER JOIN
dbo.UserInfo UserInfo_2 ON dbo.UserData.tp_SiteId = UserInfo_2.tp_SiteID AND
dbo.UserData.int4 = UserInfo_2.tp_ID LEFT OUTER JOIN
dbo.UserInfo ON dbo.UserData.tp_SiteId = dbo.UserInfo.tp_SiteID AND dbo.UserData.tp_Author = dbo.UserInfo.tp_ID LEFT OUTER JOIN
ProjectServer.dbo.MSP_WEB_PROJECTS ON
dbo.UserData.tp_ListId = ProjectServer6101.dbo.MSP_WEB_PROJECTS.WPROJ_ISSUE_LIST_NAME
WHERE (dbo.Lists.tp_Title LIKE 'Issues')
AND (dbo.Webs.ParentWebId IS NULL) AND (dbo.UserData.tp_IsCurrent = 1)

Copyright 2013. EPM Central. All rights reserved.