How to get association between site and project on PWA_Content DB RRS feed

  • Question

  • Hello everybody.

    I'm trying to get data from lists of all project sites on Project Server.

    I'm using this query to get it: 

    select c.ntext2,b.MasterUrl, c.tp_Modified
     from PWA_Content.dbo.Lists a left join PWA_Content.dbo.Webs b on b.Id=a.tp_WebId 
     left join PWA_Content.dbo.UserDataVersioned c on a.tp_ID=c.tp_ListId 
     where a.tp_Title like '%Postagens%' order by b.masterurl,c.tp_Modified

    But this query shows posts of lists Postagens of all project sites. I need to filter it by ProjectName to show only posts of one project on each search. 

    I looked for a match between projectname or projectid and something on PWA_Content.dbo.Webs or PWA_Content.dbo.Sites views. I tried to Split MasterUrl field to match project name but some projects have different site names and project names.

    Can somebody help?



    Friday, April 25, 2014 6:35 PM


  • Hi Gabriel

    I might be missing something here, couldn't understand your question fully, but if it is related to project sites, why not to query it from MSP_EPMProject_Userview from project server DB(reporting Db if version is 2010)and get it from there ?

    Something like this

         ProjectUID, ProjectName, ProjectWorkspaceInternalHRef
    FROM  MSP_EpmProject_UserView

    Thanks | Sunil Kr Singh |

    Friday, April 25, 2014 6:55 PM