Sync between PWA-view data and data in the project data base

[ThWeber]

Hi all,

I have a question concerning enterprise field values shown in the project
center view of project web access.
We are using Project Server 2003 SP3, SQL-Server 2005, Project Professional
2003 to create and maintain our project plans and using PWA to display the
data base information via IE.
My goal is,
1.) to modify enterprise field values (for example the Enterprise Project
Text37 field // 188744765) of several projects via SQL-scripting directly in
the data base,
2.) to see the changed information in the project center view of PWA.

Point 1 is not a problem. I wrote a script that modified the enterprise
field value.
Within the script I set the MSP_PROJECTS flags PROJ_READ_ONLY,
PROJ_READ_WRITE, PROJ_READ_COUNT and PROJ_LOCKED to ‘1’ before I changed the
value and at the end, I set these flags to ‘0’, plus the PROJ_EXT_EDITED and
PROJ_EXT_EDITED_TEXT flags to ‘1’. For a better understanding in the
following description, the name of the Text37 enterprise field in our
projects is “Serieâ€

Point 2 causes problems.
If I read out the value of the enterprise filed via selecting the project in
PWA and choose ‘Edit…’, the value of the enterprise field is correct!!!
It holds the value that I modified via SQL-scripting. OK so far…
But when I try to sort the project center view via
“Filter/Group/SearchàGroup by: Serieâ€, an old value of enterprise field is
used for grouping.
This behaviour is only complete in the moment, I chose the “Save and Closeâ€
button within the Project Details view or if I open the project plan RW via
Project Professional and save (publishing is not necessary) the plan. But
this is not what I want.

My Idea is to edit the enterprise fields via sql-scripting and have the new
values available in PWA without using project professional or calling ‘Edit’
and ‘Save and Close’.

From my point of view, it seams that a kind of sync between the
MSP_PROJECTS- and the MSP_WEB tables is missing. Btw, using the View
Populator Tool didn’t help. After I chose “Publish Project Plan†within the
Populator Tool, the old value is still used for the “Group by†view.

Any ideas?

Thanks in advance.

Thomas

 

[Rod Gill]

Updating directly like this is not recommended by Microsoft. Recommended
Solution is to use PDS, but Project 2007 has the new and incompatible (with
2003's PDS interface) PSI. To get the data into the PWA data set you have to
open the Project in Project professional and then publish it.

An easier, interim solution (before upgrading to 2007 or 2010) is to use VBA
code in Project Pro to read the required data and update automatically when
the project is opened and prompt the user to save and publish.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com