Query Risks & Issues from Content Database

Posted By Posted by: EPM Partners on May 2, 2013

Sometimes you need to be able to query custom fields from the default risks and issues list using SQL queries and apply conditions/filters.

Risks from Content DB

SELECT     REPLACE(w.FullUrl, ‘PWA/’, ”) AS ProjectName, d.nvarchar3 AS CurrentStatus, d.nvarchar8 AS Priority, d.nvarchar6 AS IssueType, d.nvarchar7 AS Severity,                       d.nvarchar9 AS IssueName, d.ntext2 AS ActionUpdateDiary, d.ntext3 AS FinalResolution, d.ntext5 AS IssueDescription, d.datetime2 AS DateRaised,                       d.datetime3 AS DateAssigned, d.datetime4 AS DateClosed, sql_variant3 as IssueScore FROM         Webs AS w WITH(NOLOCK) LEFT OUTER JOIN AllLists AS l WITH(NOLOCK) ON w.Id = l.tp_WebId LEFT OUTER JOIN AllUserData AS d WITH(NOLOCK) ON d.tp_ListId = l.tp_ID WHERE     (l.tp_Title = ‘issues’) AND (d.tp_IsCurrent = 1) AND (d.tp_DeleteTransactionId = 0)

Issues from Content DB

SELECT Replace(FullUrl,’PWA/’,”) as ProjectName, nvarchar3 as CurrentStatus, nvarchar6 as RiskType, nvarchar7 as Likelihood, nvarchar8 as Severity, ntext2 as RiskDescription, nvarchar9 as RiskStrategy, nvarchar10 as RiskName, ntext7 as ActionUpdateDiary, ntext8 as FinalResolution, datetime1 as TargetDate, datetime2 as DateRaised, datetime3 as DateAssigned, sql_variant3 as LikelihoodNumber, sql_variant4 as SeverityNumber, sql_variant5 as RiskScore FROM         dbo.Webs AS w WITH(NOLOCK) LEFT OUTER JOIN dbo.AllLists AS l WITH(NOLOCK) ON w.Id = l.tp_WebId LEFT OUTER JOIN dbo.AllUserData AS d WITH(NOLOCK) ON d.tp_ListId = l.tp_ID WHERE     (l.tp_Title = ‘risks’) and d.tp_IsCurrent = 1 and d.tp_DeleteTransactionId = 0

Note:*

  • Bold fields are examples and the respective fields requried from the list will need to be identified manually by querying all fields.
  • Should be used only when there is no alternative as Microsoft advises not querying the content database.

Blog Posted In Blog Posted In: How to, Reporting
Blog Posted In Comments Off on Query Risks & Issues from Content Database