none
PS 2010 how to read the Resource name for a task in a project in SQL RRS feed

  • Question

  • I have tried the follwing sql code to read the resource name for a single task but it gets wrong values. It gets a resource of many resources in the build team option of the project and not the resource name of the task. Please help.

    select

    TaskUID, r.ResourceName

    from

    ProjectServer_Reporting..MSP_EpmAssignment_UserView a,

    ProjectServer_Reporting

    ..MSP_EpmResource_UserView r

    where

    a.ResourceUID=r.ResourceUID and a.TaskUID=@TaskUID

    Wednesday, March 14, 2012 1:50 AM

Answers

  • Ok, Seems that resource data sync has issue in Reporting database. Can you please open the Resource account from PWA>Servers ettings> Manage Users> & resave it. Monitor the Manage queue job once the reporting sync job completes, Try running the same query & check the result.

    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Marked as answer by Graham1135 Wednesday, March 14, 2012 9:17 PM
    Wednesday, March 14, 2012 5:24 AM
  • Ok, so I remember renaming the user to the one I have selected in PWA right now. This put the project out of sych. Once I synched everything, the project started showing the old information again. I amended the information to the correct values, published and checked in.

    Now the query is hsowing the correct values.

    • Marked as answer by Graham1135 Wednesday, March 14, 2012 9:17 PM
    Wednesday, March 14, 2012 9:17 PM

All replies

  • HI there,

    Please see if the below simple query helps.

    SELECT 
    a.ProjectUID,
    a.TaskUID,
    r.ResourceName,
    a.ResourceUID
     FROM dbo.MSP_EpmAssignment_UserView a
     JOIN dbo.MSP_EpmResource_UserView r
     ON r.ResourceUID = a.ResourceUID
     WHERE r.ResourceIsActive = 1
     AND ResourceIsGeneric =0
     AND ResourceIsTeam =0
     AND a.TaskUID = @taskuid

    Does that help?

    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Wednesday, March 14, 2012 4:56 AM
  • I get the same result. When I check the result from project web access(front end), the "resource name" is something else and when I query it using your code or my code I get a different persons name. What could be wrong? The project has been checked in.
    • Edited by Graham1135 Wednesday, March 14, 2012 5:17 AM
    Wednesday, March 14, 2012 5:16 AM
  • Ok, Seems that resource data sync has issue in Reporting database. Can you please open the Resource account from PWA>Servers ettings> Manage Users> & resave it. Monitor the Manage queue job once the reporting sync job completes, Try running the same query & check the result.

    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    • Marked as answer by Graham1135 Wednesday, March 14, 2012 9:17 PM
    Wednesday, March 14, 2012 5:24 AM
  • I have resaved the two users in question and it still gives the same problem. I have also tried to check out the project and check it back in again. Same issue.

    Is there anything else I can do or is this a bug?

    Wednesday, March 14, 2012 8:45 PM
  • Hi there,

    What about if you create an excel services report using the OLAP cube, are the resource names correct there?

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Wednesday, March 14, 2012 9:14 PM
    Moderator
  • Ok, so I remember renaming the user to the one I have selected in PWA right now. This put the project out of sych. Once I synched everything, the project started showing the old information again. I amended the information to the correct values, published and checked in.

    Now the query is hsowing the correct values.

    • Marked as answer by Graham1135 Wednesday, March 14, 2012 9:17 PM
    Wednesday, March 14, 2012 9:17 PM