Resolution for Project Server 2002
There is only one way to permanently remove a resource in Project Server 2002. To do so, you must have access to the Project Server database using the Microsoft SQL Server 2000 Enterprise Manager. Use the following SQL script to create a stored procedure for removing resources from Project Server:
create procedure MSP_DROP_ENT_RES
@PROJ_ID INTEGER,
@RES_UID INTEGER
AS
--
-- Deletes the summary assignments for this resource.
-- This must occur first, since the query will refer
-- to the base table entries not yet implemented.
--
-- Delete the data from the binary tables first. This
-- procedure refers to the calendars table, so this
-- has to be done first.
delete from msp_res_global_binary where
proj_id = @PROJ_ID and glb_category = 1 and glb_ref_uid = @RES_UID
delete from msp_res_global_binary where
proj_id = @PROJ_ID and glb_category = 2 and glb_ref_uid in (select cal_uid
from msp_calendars where proj_id = @PROJ_ID and res_uid = @RES_UID)
--
-- Delete the data from the base tables.
--
delete from msp_resources where proj_id = @PROJ_ID and res_uid = @RES_UID
delete from msp_calendars where proj_id = @PROJ_ID and res_uid = @RES_UID
--
-- Delete the reference to the resource in the MSP_WEB_RESOURCES table.
-- delete from msp_web_resources where res_euid = @RES_UID
Warning: Before running the above SQL Server script, be sure to back up your Project Server database.
Resolution for Project Server 2003
Project Web Access provides a utility for completely deleting enterprise resources from the Project Server database. Complete the steps below to delete an resource from the Enterprise Resource Pool along with its accompanying Project Web Access user account:
1. Log into to Project Web Access with administrator permissions
2. Click Admin - Clean up Project Server database
3. Select the Resources option
4. Select the resource you wish to delete from the pick list
5. Edit the Deletion Comment text, if you desire
6. Click the Delete button
Warning: There is no undo process to reverse the deletion of an enterprise resource. The only way to retrieve a deleted project is to restore your Project Server database from the latest backup.
Note: To perform a mass deletion of projects or resources in Project Server 2003 only, download and use the Project Server Cleaner tool, available at the following link:
Download Project Server Cleaner Tool