|
|
| |
|
Issues
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
FROM dbo.Lists INNER JOIN
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)
|
| |
|
