SQL query on Published database 2007

R

Robert

Hello,

Could you help me to create a SQL query which will list all the projects
where the custom field 'My Status' has the value 'Complete' .
I didn't succeeded in finding a relationship between MSP_ PROJECT,
MSP_CUSTOM_FIELDS, MSP_LOOKUP_TABLES_VALUES,
MSP_PROJ_CUSTOM_FIELDS_VALUES....
Thanks ahead

Robert
 
R

Rod Gill

From your next post it looks like you have Project Server 2007? Look in the
Reporting db instead and the VIEW msp_EpmProject_Userview. All view names
ending with User_View are great as they automatically include all custom
fields.

Microsoft reserve the right to change draft and published databases
whenever they want in hotfixes or SP. The Reporting database is also the
only schema they have released (See Project SDK in MSDN.microsoft.com

--

Rod Gill
Microsoft MVP for Project

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

Robert

Rod,
Actually, I'm using 2007 SP1.
I know that Microsoft reserve the right to change draft and published
databases but I need to write an SQL script which will change the category
of complete projects. So I need some bootlegger information about these
relationship.
Robert
 
R

Rod Gill

Ok, that really isn't recommended, especially in the Published db!

Your best solutions are:
1 - Write PSI code to do this
2 - Write VBA code in Project to open each project that is finished with the
wrong code(get this data from the reporting db), set the correct value then
save, publish and close.

--

Rod Gill
Microsoft MVP for Project

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

Rod Gill

Sorry, third solution is to add updating status in Project Pro to close-out
process for projects so the PMs do this!

--

Rod Gill
Microsoft MVP for Project

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

James Fraser

Rod,
Actually, I'm using 2007 SP1.
I know that Microsoft reserve the right to change draft and published
databases but I need to write an SQL script which will change the category
of complete projects. So I need some bootlegger information about these
relationship.
Robert
Click to expand...

Like Rod said: Don't do this! Doing this in the Published database is
about the worst place you could be making such a change. Now the
version that PM's open won't have the change, any reports or tools
that use the official Reporting database won't show the change, and
you are introducing a big hole for project corruption to creep in.

Use the PSI for this. Or VBA code.
The PSI code will change slightly depending on if it is a lookup table
based value or not.
HEre's something to get you started:
http://blogs.msdn.com/project_progr...28/custom-field-and-lookup-table-webcast.aspx


James Fraser
 
Top