Get project issues on EPM

This is the stored procedure I am using to get issues information on my EPM

CREATE PROCEDURE [dbo].[spLoadProjectIssues]
@ProjectName NVARCHAR(255) = NULL,
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT t1.Title, t1.AssignedToResource, t1.DueDate, t1.Status, t1.CreatedDate
FROM ProjectServer_Reporting..MSP_WssIssue t1
  INNER JOIN MSP_PROJECTS t2 ON t1.ProjectUID = t2.PROJ_UID
WHERE  t2.PROJ_NAME LIKE @ProjectName
  AND (t1.CreatedDate BETWEEN @FromDate AND @ToDate)
END

Get project name and team members in EPM

SELECT t1.PROJ_UID
, t1.PROJ_NAME
, t3.RES_NAME
, CASE WHEN t1.WRES_UID = t3.RES_UID THEN 1 ELSE 0 END AS IS_PM
FROM MSP_PROJECTS t1
INNER JOIN MSP_PROJECT_RESOURCES t2 ON t1.PROJ_UID = t2.PROJ_UID
INNER JOIN MSP_RESOURCES t3 ON t2.RES_UID = t3.RES_UID
ORDER BY t1.PROJ_NAME

Get all lookup tables and their values on EPM

SELECT t1.LT_UID, t1.LT_NAME, t2.LT_VALUE_TEXT, t2.LT_VALUE_SORT_INDEX
FROM MSP_LOOKUP_TABLES t1 INNER JOIN
MSP_LOOKUP_TABLE_VALUES t2 ON t1.LT_UID = t2.LT_UID
ORDER BY t1.LT_UID, t2.LT_VALUE_SORT_INDEX

Important tables in EPM

The following tables are important tables used in Enterprise Project Management you may need to query or build your custom reports

1. MSP_PROJECTS: contains all projects
2. MSP_RESOURCES: contains all resources
3. MSP_TASKS: contains all tasks for all projects
4. MSP_ASSIGNMENTS: which tasks a resource are working on
   

Select all tasks based on each project in EPM

SELECT t2.PROJ_NAME,
 t1.TASK_NAME,
 t1.TASK_EARLY_FINISH,
 t1.TASK_LATE_START,
 t1.TASK_STOP_DATE,
 t1.TASK_RESUME_DATE,
 t1.TASK_OUTLINE_LEVEL,
 t1.TASK_DUR,
 t1.TASK_DUR_FMT,
 t1.TASK_ACT_DUR,
 t1.TASK_REM_DUR,
 t1.TASK_START_DATE,
 t1.TASK_FINISH_DATE,
 t1.TASK_ACT_START,
 t1.TASK_ACT_FINISH,
 t1.TASK_CONSTRAINT_DATE,
 t1.TASK_EARLY_START,
 t1.TASK_LATE_FINISH,
 t1.TASK_WORK,
 t1.TASK_ACT_WORK,
 t1.TASK_REM_WORK,
 t1.TASK_COST,
 t1.TASK_WBS,
 t1.TASK_SUMMARY_PROGRESS_DATE,
 t1.CREATED_DATE,
 t1.MOD_DATE
FROM dbo.MSP_TASKS t1
 INNER JOIN dbo.MSP_PROJECTS t2 ON t1.PROJ_UID = t2.PROJ_UID

Select all tasks in EPM

SELECT TASK_NAME, 
  TASK_EARLY_FINISH, 
  TASK_LATE_START, 
  TASK_STOP_DATE, 
  TASK_RESUME_DATE, 
  TASK_OUTLINE_LEVEL, 
  TASK_DUR,
  TASK_DUR_FMT,
  TASK_ACT_DUR,
  TASK_REM_DUR,
  TASK_START_DATE,
  TASK_FINISH_DATE,
  TASK_ACT_START,
  TASK_ACT_FINISH,
  TASK_CONSTRAINT_DATE, 
  TASK_EARLY_START,
  TASK_LATE_FINISH,
  TASK_WORK,
  TASK_ACT_WORK,
  TASK_REM_WORK,
  TASK_COST,
  TASK_WBS,
  TASK_SUMMARY_PROGRESS_DATE,
  CREATED_DATE,
  MOD_DATE
FROM dbo.MSP_TASKS

Select all projects in EPM

SELECT PROJ_NAME 
  PROJ_PROP_AUTHOR,
  PROJ_PROP_COMPANY,
  PROJ_INFO_CURRENT_DATE,
  PROJ_OPT_DEF_FINISH_TIME,
  PROJ_OPT_DEF_START_TIME,
  PROJ_INFO_FINISH_DATE,
  PROJ_LAST_SAVED,
  PROJ_CREATION_DATE,
  PROJ_INFO_START_DATE,
  PROJ_INFO_STATUS_DATE,
  PROJ_PROP_TITLE,
  PROJ_CHECKOUTDATE,
  WPROJ_DESCRIPTION,
  WPROJ_LAST_PUB,
  WPROJ_STS_SUBWEB_NAME,
  PROJ_SESSION_DESCRIPTION,
  PROJ_PROP_MANAGER
FROM dbo.MSP_PROJECTS

How to cancel the 'stubborn' job/queue in Project Server

When working with Project Server, sometimes you meet a failed process or failed job in Manage Queue Jobs. Its job state is 'Getting Queued'. You tried to cancel it but it's still there. In this case, you cannot force check-in the related project.
To 'kill' it and allow other jobs to run, what you need to do is enable an additional option in order to cancel the jobs.
- Login to PWA as an Administrator
- Going to Server Settings - Manage Queue.
- On the Manage Queue Jobs page, expand the Advanced Options
- Enable the "Cancel jobs getting enqueued."
- Select the particular item in the queue that's stuck and click the Cancel Jobs button.
This should cancel the blocked job and then allow the other correlated/related jobs to go through.