none
Ms Project 2007 Server - Qurey to get Status Managers name RRS feed

  • Question

  • Hi,

    Can somebody help me to write a query returning Plan Name, Task Name and Status Manager? Thanks.

    Tuesday, April 9, 2013 1:33 PM

Answers

  • Sorry, I have to apologize. I overread the version 2007 in your subject.

    TaskStatusManagerUID is only available in 2010, not in 2007. In 2007, I am only able to find an appropriate value in Published DB - at least I hope it is this column. Any queries on this table are not supported by Microsoft, so think about carefully, if you really want to do so. I hope I got the right columns, just give it a try, if you want to:

    SELECT DISTINCT dbo.MSP_EpmProject_UserView.ProjectName
    	      , dbo.MSP_EpmTask_UserView.TaskName
    	      , dbo.MSP_EpmResource_UserView.ResourceName
    FROM  ProjectServer_Published.dbo.MSP_ASSIGNMENTS
      INNER JOIN 
      	dbo.MSP_EpmResource_UserView 
    	ON CAST(ProjectServer_Published.dbo.MSP_ASSIGNMENTS.WRES_UID_MANAGER AS varchar(50)) 
             = CAST(dbo.MSP_EpmResource_UserView.ResourceUID AS varchar(50)) 
      INNER JOIN 
    	dbo.MSP_EpmProject_UserView 
      INNER JOIN 
    	dbo.MSP_EpmTask_UserView 
    	ON dbo.MSP_EpmProject_UserView.ProjectUID 
    	= dbo.MSP_EpmTask_UserView.ProjectUID 
    	ON ProjectServer_Published.dbo.MSP_ASSIGNMENTS.TASK_UID 
    	= dbo.MSP_EpmTask_UserView.TaskUID

    I hope this will help?

    Regards
    Barbara

    Wednesday, April 10, 2013 6:06 AM
    Moderator

All replies

  • Hi,

    here you are (on Reporting DB).

    SELECT     dbo.MSP_EpmProject_UserView.ProjectName
    		 , dbo.MSP_EpmTask_UserView.TaskName
    		 , dbo.MSP_EpmResource_UserView.ResourceName
    FROM         dbo.MSP_EpmProject_UserView 
    	INNER JOIN dbo.MSP_EpmTask_UserView 
    	ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTask_UserView.ProjectUID 
        INNER JOIN dbo.MSP_EpmResource_UserView 
        ON dbo.MSP_EpmTask_UserView.TaskStatusManagerUID = dbo.MSP_EpmResource_UserView.ResourceUID
    Regards
    Barbara
    Tuesday, April 9, 2013 4:44 PM
    Moderator
  • Thanks Barbara, I am getting the following error:

    Msg 207, Level 16, State 1, Line 7

    Invalid column name 'TaskStatusManagerUID'.

    Tuesday, April 9, 2013 7:21 PM
  • Sorry, I have to apologize. I overread the version 2007 in your subject.

    TaskStatusManagerUID is only available in 2010, not in 2007. In 2007, I am only able to find an appropriate value in Published DB - at least I hope it is this column. Any queries on this table are not supported by Microsoft, so think about carefully, if you really want to do so. I hope I got the right columns, just give it a try, if you want to:

    SELECT DISTINCT dbo.MSP_EpmProject_UserView.ProjectName
    	      , dbo.MSP_EpmTask_UserView.TaskName
    	      , dbo.MSP_EpmResource_UserView.ResourceName
    FROM  ProjectServer_Published.dbo.MSP_ASSIGNMENTS
      INNER JOIN 
      	dbo.MSP_EpmResource_UserView 
    	ON CAST(ProjectServer_Published.dbo.MSP_ASSIGNMENTS.WRES_UID_MANAGER AS varchar(50)) 
             = CAST(dbo.MSP_EpmResource_UserView.ResourceUID AS varchar(50)) 
      INNER JOIN 
    	dbo.MSP_EpmProject_UserView 
      INNER JOIN 
    	dbo.MSP_EpmTask_UserView 
    	ON dbo.MSP_EpmProject_UserView.ProjectUID 
    	= dbo.MSP_EpmTask_UserView.ProjectUID 
    	ON ProjectServer_Published.dbo.MSP_ASSIGNMENTS.TASK_UID 
    	= dbo.MSP_EpmTask_UserView.TaskUID

    I hope this will help?

    Regards
    Barbara

    Wednesday, April 10, 2013 6:06 AM
    Moderator
  • Thanks Barbara, it helped.
    Thursday, April 11, 2013 4:48 PM