Editing directly in SQL Enterprise Manager- MSP_VIEW_PROJ_PROJECTS

[Erin]

I had a field in EnterpriseText9 that was currently populated with numbers so
therefore it was not sorting correctly -- I then created a new number field
and redefined that text9 to a field using text and a drop down value list.
My issue was the numbers did NOT go away -- but stayed populated in the
projects until people actually went in and updated the info in project >
project information. Not all projects have been updated and instead of doing
this the way they would update it i opened up my database on my SQL 2000
server in SQL Enterprise Manager and went to the MSP_VIEW_PROJ_PROJECTS_ENT
table and scrolled over to EnterpriseText9 and just manually deleted the
numbers that were still sitting there are bad data -- it looks great in the
tables, and looks great in VS.net on the SQL server when i call up a query
for SRS -- but when i go over to microsoft web access -- it's not showing up
and looking in the actual ms project office app under project > project
information -- it's still showing the numbers still sitting in those
respective projects --- where does web access look to get that data -- i'm
looking at it in a view i created -- but the table data in the above stated
table does not show those numbers -- am i missisng a publish funciton, or a
sync between my two servers? I've refreshed the tables in the SQL Enterprise
Manager -- do i need to do a server reboot on both of these? Please advise
and thank you in advance!

(e-mail address removed)

-erin

 

[Ray McCoppin]

This is a very confusing area. Project Pro data always comes from the MSP
tables. Where as PWA appears to come from the MSP_VIEW tables for most
detail data on tasks and assignments. But the project information in PWA is
calling the PDS and getting data from the MSP tables. You will need to
remove the data from the MSP_TEXT_FIELDS table, and be sure to set the
external edit flag in the MSP_PROJECTS table.

Hope this helps

 

[Erin]

How would one find the TextFieldID for EnterpriseTextfield9? I see what you
are saying on this -- is it BP to delete the data in the
MSP_VIEW_PROJ_PROJECTs_ENT table as well as the MSP_TEXT_FIELDS table?

Also at the risk of sounding unintelligent -- how and where do i set this
external edit flag and it is required -- this won't affect any edits and
publishing on the front end that my project managers do through project >
project information will it? I just need a way to get this old data out of
these enterprise fields and i also have a need for it since i'm running into
a character restriction on a calculation field that's much easier just to
manually input with a script directly in to the DB - so i will be using this
direct db edit feature a lot.

thanks --
erin

 

[Erin]

I would want to set the external edit flag to 1 instead of the existing 0
correct? I will wait to make sure this won't mess up the PM's normal way of
editing nad publishing projects when you or another replies -- thanks so much!

(e-mail address removed)

 

[Ray McCoppin]

TEXT_FIELD_ID Refers to a valid CONV_VALUE in the MSP_CONVERSIONS table.

Yes delete the data from both tables.

You are going to need to write a SQL script to do this. The external edit
flags are in the MSP_Projects table, and will not effect the values from
Project Pro unless the project is checked out.

You should look a the following link and read the PJDB.HTM file
http://office.microsoft.com/en-us/projservadmin/HA011642711033.aspx


Hope this helps

 

[Erin]

i will try this now -- do i need to change the other EXT_EDITED -- fields? I
saw this on a posting:
You have to change PROJ_EXT_EDITED to 1
update MSP_PROJECT set PROJ_EXT_EDITED=1 where PROJ_ID=..

you only mention the EXT_EDITED_FLAG -- do i need to do this for the
----TEXT, and plain EXT_EDITED? I appreciate your help and insight! I will
read that doc thank you for the reference.

 

[Erin]

Here's something odd -- the value for Task Enterprise Text 9 is 188744487 --
not in the TEXT_FIELDS_ID table gets better and better this AM I do
know the data very well and could sort by the project ID and look at it but
it seems tedious?

 

[Erin]

scratch that -- i was not looking at Task Enterprise PROJECT Text 9 --- which
is 188733737 --

 

[Erin]

Ray -- I did all this that you stated -- is there a lag in the actual update
for the apps server where PWA would sit on? Just in case i've ordered a
server reboot for the SQL and for the PWA tonight to check. I deleted the
values on both tables, and set all the PROJ_EXT_EDITED ---"" fields to 1
instead of 0....
should this change have been instant in PWA?

Thanks

(e-mail address removed)

 

[mars]

Is there a "front door" approach to re-use an enterprise field and
clear out the old data or does it always require database editing with
sql scripts through the "back door"?

Thanks,
Greg

 

[Erin]

I must be missing something -- I've set all ext edit flags:
PROJ_EXT_EDITED_DATE, --_DUR, --_NUM, --_FLAG, --_CODE, --_TEXT AND THE
PROJ_EXT_EDITED FROM MSP_PROJECTS TO 1, CLEARED VALUES FROM MSP_TEXT_FIELDS,
AND FROM MSP_VIEW_PROJECTS_PROJ_ENT ??????

I did a reboot of the servers, the SQL DB looks fine, but web access still
shows these values -- should i delete the view and recrete it?

thanks
(e-mail address removed)

 

[Gary L. Chefetz [MVP]]

Erin:

You need to open and recalculate each project that the new data affects. The
calclation and the resetting of the flags is done by the Project Client.
Essentially, the ext edited fields are there to tell the Projec client to
rebuild the binary record of the project using the new data you provided.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Erin]

Gary,

Thanks -- i am in the middle of doing that now i figured a re-publish
was in order after i saw the data was deleted from project - project
info...is there a quick way to do a republish of all projects? i have 400+
of them

 

[Gary L. Chefetz [MVP]]

Erin:

See if this gives you some ideas:

http://www.projectserverexperts.com/Shared Documents/Copyright.htm

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

 

[Erin]

Thanks Gary

Erin:

See if this gives you some ideas:

http://www.projectserverexperts.com/Shared Documents/Copyright.htm

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com