SQL to return task "Resource Names"

[Matt R]

Resource Names isn't a true task-level field.

As I understand it, to get the resources for a task in sql, you must walk
the project to the task=>to assignments=>to resource to get all resources
assigned to a task.

Does anyone know of an easy way to (or out of the box stored proc or
function) in project server database that will return all resources assigned
to a task simply by providing the task GUID?

Thanks!

 

[Jack Dahlgren MVP]

Your understanding is correct. Resources are not tasks. When a resource and
a task come together it is called an assignment.
Tasks may have multiple assignments if there are multiple resources.
I think you may have to make a join on the tables to get the assignment, but
it should be a simple query.

-Jack

 

[Jonathan Sofer [MVP]]

Here is a table function you could use and how you would call it.

CREATE FUNCTION [dbo].[_fn_tbl_GetResourceAssignments] (@ProjectUID
uniqueidentifier, @TaskUID uniqueidentifier)
RETURNS TABLE
AS
RETURN
(
SELECT MSP_EpmResource.ResourceName
FROM MSP_EpmAssignment INNER JOIN
MSP_EpmResource ON MSP_EpmAssignment.ResourceUID =
MSP_EpmResource.ResourceUID AND
MSP_EpmAssignment.ResourceOwnerUID =
MSP_EpmResource.ResourceUID INNER JOIN
MSP_EpmTask ON MSP_EpmAssignment.ProjectUID =
MSP_EpmTask.ProjectUID AND
MSP_EpmAssignment.TaskUID = MSP_EpmTask.TaskUID INNER
JOIN
MSP_EpmProject ON MSP_EpmAssignment.ProjectUID =
MSP_EpmProject.ProjectUID AND MSP_EpmTask.ProjectUID =
MSP_EpmProject.ProjectUID
WHERE MSP_EpmProject.ProjectUID = @ProjectUID AND
MSP_EpmTask.TaskUID = @TaskUID
)

select * from dbo._fn_tbl_GetResourceAssignments
('10BD295E-6620-46A0-B184-909FF89FDD47',
'D36342D5-C0BD-4D2F-91BE-0866D6620EC7')

 

[Matt R]

Will try this & let you know. Thank you.

Here is a table function you could use and how you would call it.

CREATE FUNCTION [dbo].[_fn_tbl_GetResourceAssignments] (@ProjectUID
uniqueidentifier, @TaskUID uniqueidentifier)
RETURNS TABLE
AS
RETURN
(
SELECT MSP_EpmResource.ResourceName
FROM MSP_EpmAssignment INNER JOIN
MSP_EpmResource ON MSP_EpmAssignment.ResourceUID =
MSP_EpmResource.ResourceUID AND
MSP_EpmAssignment.ResourceOwnerUID =
MSP_EpmResource.ResourceUID INNER JOIN
MSP_EpmTask ON MSP_EpmAssignment.ProjectUID =
MSP_EpmTask.ProjectUID AND
MSP_EpmAssignment.TaskUID = MSP_EpmTask.TaskUID INNER
JOIN
MSP_EpmProject ON MSP_EpmAssignment.ProjectUID =
MSP_EpmProject.ProjectUID AND MSP_EpmTask.ProjectUID =
MSP_EpmProject.ProjectUID
WHERE MSP_EpmProject.ProjectUID = @ProjectUID AND
MSP_EpmTask.TaskUID = @TaskUID
)

select * from dbo._fn_tbl_GetResourceAssignments
('10BD295E-6620-46A0-B184-909FF89FDD47',
'D36342D5-C0BD-4D2F-91BE-0866D6620EC7')

Resource Names isn't a true task-level field.

As I understand it, to get the resources for a task in sql, you must walk
the project to the task=>to assignments=>to resource to get all resources
assigned to a task.

Does anyone know of an easy way to (or out of the box stored proc or
function) in project server database that will return all resources
assigned
to a task simply by providing the task GUID?

Thanks!

 

[Matt R]

Very Awesome Jonathan. This worked verbatim, and in my case a slight change
is all it took. Thanks a lot man, great job--and great expertise.

Here is a table function you could use and how you would call it.

CREATE FUNCTION [dbo].[_fn_tbl_GetResourceAssignments] (@ProjectUID
uniqueidentifier, @TaskUID uniqueidentifier)
RETURNS TABLE
AS
RETURN
(
SELECT MSP_EpmResource.ResourceName
FROM MSP_EpmAssignment INNER JOIN
MSP_EpmResource ON MSP_EpmAssignment.ResourceUID =
MSP_EpmResource.ResourceUID AND
MSP_EpmAssignment.ResourceOwnerUID =
MSP_EpmResource.ResourceUID INNER JOIN
MSP_EpmTask ON MSP_EpmAssignment.ProjectUID =
MSP_EpmTask.ProjectUID AND
MSP_EpmAssignment.TaskUID = MSP_EpmTask.TaskUID INNER
JOIN
MSP_EpmProject ON MSP_EpmAssignment.ProjectUID =
MSP_EpmProject.ProjectUID AND MSP_EpmTask.ProjectUID =
MSP_EpmProject.ProjectUID
WHERE MSP_EpmProject.ProjectUID = @ProjectUID AND
MSP_EpmTask.TaskUID = @TaskUID
)

select * from dbo._fn_tbl_GetResourceAssignments
('10BD295E-6620-46A0-B184-909FF89FDD47',
'D36342D5-C0BD-4D2F-91BE-0866D6620EC7')

Resource Names isn't a true task-level field.

As I understand it, to get the resources for a task in sql, you must walk
the project to the task=>to assignments=>to resource to get all resources
assigned to a task.

Does anyone know of an easy way to (or out of the box stored proc or
function) in project server database that will return all resources
assigned
to a task simply by providing the task GUID?

Thanks!

 

[Jonathan Sofer [MVP]]

Great, I'm glad it worked! Thanks for the feedback.

Very Awesome Jonathan. This worked verbatim, and in my case a slight
change
is all it took. Thanks a lot man, great job--and great expertise.

Here is a table function you could use and how you would call it.

CREATE FUNCTION [dbo].[_fn_tbl_GetResourceAssignments] (@ProjectUID
uniqueidentifier, @TaskUID uniqueidentifier)
RETURNS TABLE
AS
RETURN
(
SELECT MSP_EpmResource.ResourceName
FROM MSP_EpmAssignment INNER JOIN
MSP_EpmResource ON MSP_EpmAssignment.ResourceUID =
MSP_EpmResource.ResourceUID AND
MSP_EpmAssignment.ResourceOwnerUID =
MSP_EpmResource.ResourceUID INNER JOIN
MSP_EpmTask ON MSP_EpmAssignment.ProjectUID =
MSP_EpmTask.ProjectUID AND
MSP_EpmAssignment.TaskUID = MSP_EpmTask.TaskUID
INNER
JOIN
MSP_EpmProject ON MSP_EpmAssignment.ProjectUID =
MSP_EpmProject.ProjectUID AND MSP_EpmTask.ProjectUID =
MSP_EpmProject.ProjectUID
WHERE MSP_EpmProject.ProjectUID = @ProjectUID AND
MSP_EpmTask.TaskUID = @TaskUID
)

select * from dbo._fn_tbl_GetResourceAssignments
('10BD295E-6620-46A0-B184-909FF89FDD47',
'D36342D5-C0BD-4D2F-91BE-0866D6620EC7')

Resource Names isn't a true task-level field.

As I understand it, to get the resources for a task in sql, you must
walk
the project to the task=>to assignments=>to resource to get all
resources
assigned to a task.

Does anyone know of an easy way to (or out of the box stored proc or
function) in project server database that will return all resources
assigned
to a task simply by providing the task GUID?

Thanks!