Deleting Project with Rejected Timesheet Lines
I came across a situation that when I tried deleting a project from PWA, the project deletion failed because it had outstanding rejected timesheets. Looking in the queue I found the following error messages. This occurred on Project Server 2010. A CU was issued to fixed this bug, but in my situation where I could not apply any patches until the end of year. Also, the project was a test project and needed to be deleted, because it was missing up the resource allocation.
When deleting a project I get the following errors.
Collections:
rejectedTimesheetLines (System.Guid) :
Item value = 'cfe6ca01-bdae-49d8-ad41-0262d01f1fe7' :
Error: id='10000' name='GeneralItemDoesNotExist' uid='7b4279e5-c8a3-45af-a9ac-c050f232c09e'
Error: id='10000' name='GeneralItemDoesNotExist' uid='d87dff17-3e3c-4ac8-a63e-854f79fe725c'
Error: id='10000' name='GeneralItemDoesNotExist' uid='3910e065-30bd-454f-8911-164910232fd3'
General Error:
ProjectDeleteFailure (23006). Details: id='23006' name='ProjectDeleteFailure' uid='e94c8143-7d28-4fa1-acb0-5e8aefb3adab' projectuid='05cfcc25-6a13-46e3-ab5c-d173942af6c5' messagetype='Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.AdjustTimeSheetForDeletedProjectMessage' messageID='6' stage='' blocking='Undefined'.
Queue:
GeneralQueueJobFailed (26000) - ProjectDelete.AdjustTimeSheetForDeletedProjectMessage. Details: id='26000' name='GeneralQueueJobFailed' uid='4768f5c6-52ee-4679-83e8-81c04002d2a0' JobUID='0ef10d90-9319-4979-b49f-da8f3c9a9ece' ComputerName='SP07' GroupType='ProjectDelete' MessageType='AdjustTimeSheetForDeletedProjectMessage' MessageId='6' Stage=''. For more details, check the ULS logs on machine SP07 for entries with JobUID 0ef10d90-9319-4979-b49f-da8f3c9a9ece.
I called the MS Support team and they provided me the following query to delete all the timesheets. I probably could just delete the rejected timesheets, but this particular project was a test project and so none of the timesheet information was needed.
Here is what we did to resolve
Backup all the Project Server databases
Execute the SQL statements below
From PWA / Server Settings / Delete Objects/ Delete Published projects.
USE ProjectServer_Published
--
-- The Proj_UID should match the Proj_UID in query below
--
SELECT PROJ_NAME, PROJ_UID
FROM MSP_PROJECTS
WHERE PROJ_NAME like '%enter name of project%'
--
-- Make note of rows selected for reference
-- Replace the UID with number found above
--
SELECT * FROM MSP_TIMESHEET_ACTUALs
where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID
FROM MSP_TIMESHEET_ACTIONS A
INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID
WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')
--
-- Query to remove rows
--
DELETE MSP_TIMESHEET_ACTUALs
where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID
FROM MSP_TIMESHEET_ACTIONS A
INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID
WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')
--
-- Make note of number of rows selected
--
SELECT * FROM MSP_TIMESHEET_LINES
where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID
FROM MSP_TIMESHEET_ACTIONS A
INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID
WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')
--
-- Query to remove rows
--
delete MSP_TIMESHEET_LINES
where ts_line_UID IN (SELECT DISTINCT A.TS_LINE_UID
FROM MSP_TIMESHEET_ACTIONS A
INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID
WHERE A.TS_ACTION_ENUM = 1 AND L.PROJ_UID = '05CFCC25-6A13-46E3-AB5C-D173942AF6C5')