Timesheet <-> tasks Reporting DB SQL Problem (bug?)

[James Fraser]

[Never sure whether Project Server SQL stuff belongs here or in
server, but...]

I found the workaround, but I thought I'd post in case I'm missing
something, to help someone else, or so my searches on this problem in
a year give me some results. (You know you've been using USEnet too
long when your searches return your own posts, and they actually have
the answer you're looking for.)

The TaskUID in the MSP_TimesheetTask Table in the Reporting database
seems to often be incorrect and not link to any actual task. If I join
to the task through the assignment (MSP_TimesheetLine -> MSP_Epm
Assignment -> MSP_EpmTask) then I get back a valid task

Am I missing something?

Here's a query that I would expect to not return any results but I am
getting hundreds:


SELECT
tt.TaskName AS TimeSheetTaskName,
met.TaskName AS TaskJoinedfromTimesheetTask,
met2.TaskName TaskJoinedfromAssignment,
mea.[TaskUID] AS AssnTASKUID,
tt.[TaskUID] AS TimesheetTaskUID
FROM
[MSP_TimesheetLine] AS mtl
INNER JOIN
[MSP_TimesheetTask] AS tt
ON tt.[TaskNameUID] = mtl.[TaskNameUID]
LEFT OUTER JOIN
[MSP_EpmTask] AS met
ON tt.[TaskUID] = met.[TaskUID]

LEFT OUTER JOIN
[MSP_EpmAssignment] AS mea
ON mtl.[AssignmentUID] = mea.[AssignmentUID]
LEFT OUTER JOIN
[MSP_EpmTask] AS met2
ON met2.TaskUID = mea.[TaskUID]

WHERE met.TaskName IS NULL
AND met2.TaskName IS NOT NULL



James Fraser