Resources in MSP_RESOURCES table

[Ravi]

Hello all,

I see a resource having more than one entry on MSP_RESOURCES table.
Also each row has different project id. My assumption is that the
table MSP_RESOURCES will have n rows for a resource if he is a project
team member on n projects that are published on server. Is this
correct? If not why am seeing more than one entry for a single
resource with different project id? Please help me explaining the fact
behind. Thank you in advance.

Thanks,
Ravi.

 

[Rod Gill]

Which version of project server? MSP_Resources should only have one copy of
each resource unless you imported a project and didn't map a local resource
correctly to its enterprise resource equivalent. The assignment table is the
one that has a record for each resource on each task in each project.

--

Rod Gill
Microsoft MVP for Project

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

 

[Ravi]

Which version of project server? MSP_Resources should only have one copy of
each resource unless you imported a project and didn't map a local resource
correctly to its enterprise resource equivalent. The assignment table is the
one that has a record for each resource on each task in each project.

--

Rod Gill
Microsoft MVP for Project

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








- Show quoted text -

Thank you for your reply Rod! This is on 2003 version. I understand
that only assignments to have multiple rows for a resource (for each
task assignment), however I saw a PROJ_ID column on MSP_RESOURCES
table. So what for this column is added to this table?

Thanks,
Ravi.

 

[Stephen Sanderlin]

Ravi,

I believe what Rod is saying is that you should see at most one RES_UID
in the MSP_RESOURCES table per PROJ_ID, unless you've incorrectly
mapped something.

However, you may see multiple entries of the same RES_UID, each with a
different PROJ_UID.

This allows you to retrieve a list of the resources within a project,
regardless of whether a resource has an assignment within that project
or not.

The following query should make it clearer for you to see:

SELECT
MRES.RES_ID,
MRES.RES_UID,
MRES.RES_NAME,
MPROJ.PROJ_NAME
FROM
MSP_RESOURCES MRES
INNER JOIN
MSP_PROJECTS MPROJ ON MPROJ.PROJ_ID = MRES.PROJ_ID
WHERE MRES.RES_UID > 0
AND MRES.RES_TYPE = 1

HTH,
Steve

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.

 

[Ravi]

Ravi,

I believe what Rod is saying is that you should see at most one RES_UID
in the MSP_RESOURCES table per PROJ_ID, unless you've incorrectly
mapped something.

However, you may see multiple entries of the same RES_UID, each with a
different PROJ_UID.

This allows you to retrieve a list of the resources within a project,
regardless of whether a resource has an assignment within that project
or not.

The following query should make it clearer for you to see:

SELECT
        MRES.RES_ID,
        MRES.RES_UID,
        MRES.RES_NAME,
        MPROJ.PROJ_NAME
FROM
        MSP_RESOURCES MRES
INNER JOIN
        MSP_PROJECTS MPROJ ON MPROJ.PROJ_ID = MRES.PROJ_ID
WHERE   MRES.RES_UID > 0
AND     MRES.RES_TYPE = 1

HTH,
Steve

--
Stephen Sanderlin
Owner/Founder - EPMFAQhttp://www.epmfaq.com/http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Serviceshttp://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.






- Show quoted text -

Thank you Steve!

Running the query that you gave, returned five entries of same
resource for five different projects. No duplicate on each project.
So my assumption is correct which is "a resource will have one entry
per project on which resource is a part, but with different RES_UID".
Or still I've taken it wrong.

Thanks,
Ravi.

 

[Stephen Sanderlin]

RES_UID should be unique for each resource. If a resource appears on
multiple projects, their RES_UID should be different -- unless you're
looking at two versions of the same project.

RES_ID represents the resource's position in Project's Resource Sheet.

Resources are linked to the Enterprise Resource Pool through RES_EUID.

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.