Where are the Enterprise Custom Fields in Project Server 2007?

[Andy S]

I am running Project Server 2007. We have a created a Enterprise
Custom Field titled Owning Org to identify the organization each
uploaded schedule belongs to. It is a text field tied to each
project.

I am now looking for where this data resides in SQL for projects that
have been uploaded to the server, but not published. Does anyone know
which table in the DRAFT database that these information is stored?

 

[Chak]

Hi Andy,

You can find the info in following tables:

dbo.MSP_PROJ_CUSTOM_FIELD_VALUES

dbo.MSP_LOOKUP_TABLE_VALUES

dbo.MSP_PROJECT_LOOKUP_TABLES

You have to join the above tables to find the value for each project.

Please make sure, you are not doing any updates to Draft database by
using SQL. If you are looking for just query info, it may be okay.


Thanks
Chak
http://www.epmcentral.com

 

[Andy S]

Draft database's MSP_LOOKUP_TABLE_VALUES has only one entry, which is
all null. However, the published database's MSP_LOOKUP_TABLE_VALUES
has the values I am looking for. Is it okay to mix and match Draft
and Publish databases in regards to lookup tables?

 

[Chak]

Hi Andy,

I forgot to ask you, what's the reason you want to see Draft database
tables?

If your project plan is published, you can get the info in Published
and Reporting databases. You can use Reporting database tables which
are denormalized structure and easy to query.

Please look for dbo.MSP_EpmProject_UserView in Reporting database
under Views. You will see the Enterprise custom columns here.

Thanks
Chak
http://www.epmcentral.com

 

[Piet Remen]

Good point Chak. Andy, the reporting database is definitely the preferred
method of querying data from project server. You should only need to query
the other DB's on rare occasions. The views in the reporting database are
dynamically built. As Chak said, you can query dbo.MSP_EpmProject_UserView
for for project level information and project entity enterprise fields. For
task info and task entity custom fields use, MSP_EpmTask_UserView and for
resource info and resource entity fields reference, MSP_EpmResource_UserView.
Note the custom fields are always at the end of the view columns.

 

[Esther Stamerjohn]

We also running 2007 and using the view mentioned, but are getting null
values for the custom field, project code. We have published the projects
and I can see the project codes from the project center view, but when we try
our own sql the values are null. We have SP1 installed. Has anyone seen
this problem?
Esther

 

[Piet Remen]

Hmmm, interesting one Esther. Have you checked the Project Server queue to
see if anything is failing in there. Quite often reporting database
sychronisation failures will not block the queue correlation but might
explain if the reporting database values are out of synch with the publish
database. This of course is assuming your SQL is reading from the project
server reporting database. Any chance of posting your SQL to confirm the
query?

 

[Esther Stamerjohn]

Yes, we found that to be the case. Then we tracked it down to some of the
resources not being in the published database. Using SQL we got a list of
the resources and then did a save on each. Then we republished the project
and the jobs were successful. It turned out the problem was not even the
custom fields.
Esther

 

[Piet Remen]

I suspected there was an issue wiith the report database synchronisation.
Good work tracking it down Esther. Have a good day!

Regards,

Piet Remen