none
Where are the custom enterprise field values stored in SQL? RRS feed

  • Question

  • Hello,

    We're using Project Server 2010 with SQL 2008.

    Our Cognos developer needs to create some custom reports for our PM's based on a bunch of custom enterprise fields that I've created.  I'm no DBA, but I've been tasked with finding where these values are stored in SQL (table,  column etc..).  I've managed to find some of the fields and their values within some userview tables, but I'm assuming that they're pulling that data from another table somewhere...which I can't find.

    Any help would be greatly appreciated!

    Thanks,

    Andre

    Friday, September 6, 2013 6:31 PM

Answers

  • Hello, Use the Project Server Reporting database views, you will see user views for entities such as projects, tasks, assignments etc. here you will find custom field values. The only custom fields you won't find in these views are the custom fields that allow multi values from lookup tables, these are stored differently. Some links below that will help. Project SDK - reporting db schema: http://msdn.microsoft.com/en-us/library/ms512767(v=office.14).aspx Accessing multi value custom fields: http://pwmather.wordpress.com/2011/09/07/accessing-custom-fields-that-allow-multiple-values-from-look-up-tables-in-projectserver-via-t-sql-msproject-ps2010-ps2007/. Paul

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

    • Marked as answer by Andre Janveaux Monday, September 9, 2013 4:07 PM
    Friday, September 6, 2013 7:55 PM
    Moderator

All replies

  • Hello, Use the Project Server Reporting database views, you will see user views for entities such as projects, tasks, assignments etc. here you will find custom field values. The only custom fields you won't find in these views are the custom fields that allow multi values from lookup tables, these are stored differently. Some links below that will help. Project SDK - reporting db schema: http://msdn.microsoft.com/en-us/library/ms512767(v=office.14).aspx Accessing multi value custom fields: http://pwmather.wordpress.com/2011/09/07/accessing-custom-fields-that-allow-multiple-values-from-look-up-tables-in-projectserver-via-t-sql-msproject-ps2010-ps2007/. Paul

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

    • Marked as answer by Andre Janveaux Monday, September 9, 2013 4:07 PM
    Friday, September 6, 2013 7:55 PM
    Moderator
  • Hi Paul,

    Thanks for replying.  So I should use a table such as MSP_EPMAssigment_UserView?  I did find all of the values for the fields I created in there, I just figured it was pulling the data from another table.

    I'll give it shot, thanks!

    André

    Friday, September 6, 2013 9:17 PM
  • That is correct. You will find tasks, projects, timesheet etc user views also. Paul

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

    Friday, September 6, 2013 9:29 PM
    Moderator
  • There are continuous enhancements for the Project Server databases to facilitate task operations, such as backup and restore, migration, etc. however, many tables and views still do the same job in all Project Server versions with some changes like the database name and schema …etc.

    To query custom fields in Project Server 2019,2016,2013 and 2010, Please check Query Enterprise Custom Fields from Project Server Database

    Note: Fields with a lookup table multiple values enabled will not be listed directly in “MSP_EpmProject_UserView”. it will be stored in an association table as mentioned at Query Multiple Values Lookup Table Field in Project Server.


    [About Me] | [SPGeeks Blog] | [deBUG.to]

    Monday, August 31, 2020 8:55 PM