Delete project-level custom fields using sql

[Kris R.]

***Project Server 2000***
I'm trying to delete 2 enterprise project-level custom fields (one date,
one
number). The SQL I'm using is this:

--delete ProjectEnterpriseDate1 (Deadline) from date fields table
DELETE FROM ProjectServer.dbo.MSP_Date_Fields where Proj_ID = @Proj_ID AND
Date_Field_ID = 188744539
--delete ProjectEnterpriseNumber1 (Warning) from number fields table
DELETE FROM ProjectServer.dbo.MSP_Number_Fields where Proj_ID = @Proj_ID
AND
Num_Field_ID = 188744690
--delete both from view table
Update ProjectServer.dbo.MSP_View_Proj_Projects_Ent set
ProjectEnterpriseDate1 = null, ProjectEnterpriseNumber1 = null
WHERE Ent_ProjectUniqueID = @Proj_ID
--flip externally edited bits
Update ProjectServer.dbo.MSP_Projects SET Proj_Ext_Edited_Date = 1,
Proj_Ext_Edited_Num = 1, Proj_Ext_Edited = 1
WHERE Proj_ID = @Proj_ID

After executing this, the values are indeed gone from the specified tables.
BUT, when I open the project in Pro, and open the "Project Information"
window, these values are still present. I tried forcing calculations, but
the values are still there.

What am I missing in the delete?

Thanks,
Kris R.

 

[Rod Gill]

Hi,

The MSP_ tables relate to project data. The Project Server data is in the
server tables. Read the projsrvr.htm file in one of project's program
folders. This sort of thing is more safely done by editing the Enterprise
Global from within Project whilst logged in as an admin person.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.project-systems.co.nz/
Email rodg AT project-systems DOT co DOT nz

 

[Kris R.]

Um, don't quite know what you mean by "project server data" being in
"server" tables. There are no "server" tables - just tables in the Project
Server database. None have "server" in the name. And, all data is project
server data - it's in the database on the server. I'm quite familiar with
the database and tables, and usually know where data is being stored -
that's why I am so mystified as to why this is not working apparently.

And, of course, this is "safer" doing it manually, but the client needs it
done programmatically. I've spent the last 3 years programming against the
PS database, both directly and with the PDS, with no ill-effects, as we take
great care in how we implement things.

I still need to know where the data is coming from, that I see in the
"Project Information" dialog, since deleting it from the fields and views
tables doesn't seem to do it.

Any other ideas?

Thx.

 

[Rod Gill]

I don't think any of the Enterprise fields are stored in the plain MSP_
tables. Look in the pjsrvr.htm file installed in one of project's program
folders.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.project-systems.co.nz/
Email rodg AT project-systems DOT co DOT nz

 

[Jono]

Hi Kris...
Just seen your post...I am having problems finding where data is being
stored relating to Enterprise Fields. I am trying to add additional
Enterprise fields as the 10 cost fields is not enough. If you have found the
answer to your origional question it might help me with my question.....Let
me know if you have sorted it.

Cheers
Jono

 

[Kris R.]

As far as I know, you can't add any more custom fields than are defined. At
least we've never found out how.

You can see the list of available custom fields in MSP_Conversions (this is
also where you find each field's "internal" ID value: 188744315, 1887844316,
etc)

When a custom field is actually assigned a value, then it is stored in the
appropriate table: MSP_Number_Fields (which also hold cost values),
MSP_Date_Fields, etc.

Hope this is of some help,
Kris

 

[Kris R.]

On the contrary, when you assign a value to a custom field, it is stored in
tables such as:
MSP_Number_Fields
MSP_Date_Fields
MSP_Duration_Fields
etc.

As I said, I'm quite familiar with the schema for the database - been
programming against it for multiple versions of Project Server. That's why
this is stumping me - AFAIK, my SQL should work, but it doesn't.

*sigh*

Kris

 

[Jono]

Thanks for the info Kris. Ive got to get this done so when I have sorted it I
will post the procedure.

Jono

 

[Kris R.]

So, I'm still not able to successfully delete these values.

Any other suggestions...?

Thanks,
Kris R.