Article ID: 2933444 - View products that this article applies to.
Expand all | Collapse all

Summary

This article describes the steps for running the below SQL script which will update the following two stored procedures. 

draft.MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES
pub.MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES


The same updates to both of these stored procedures are expected to be released as part of the upcoming April 2014 Cumulative Update (CU). In the interim, customers who have Project Server 2013 deployed, are highly encouraged to go through this procedure to ensure they are protected from the missing resources condition as documented in following blog post: 

http://blogs.technet.com/b/projectsupport/archive/2014/02/06/project-server-2013-and-project-online-what-happened-to-my-resources.aspx


More information


Note There are no requirements of having to apply any Cumulative Updates before going through this procedure. However be aware that if you apply any Cumulative Update, or Service Pack, afterwards, remember to reapply the changes by going through the steps in this article again. Once the April 2014 CU is released, the procedure in this article will no longer be needed and customers will just have to apply the April CU to receive the changes in this article. 


Instructions for running the below SQL script.

This operation is typically best handled by a database administrator or an administrator who is familiar with managing the Project Server database.
  1. Launch SQL Server Management Studio. 
  2. When prompted, specify the SQL Server name and then click OK
  3. Once connected, open a New Query window and ensure you are connected to your Project Server database. For this, click File, click New and then click Query with Current Connection
  4. Copy the entire block of SQL script from the code section below and paste it into the New Query window that you just opened. 
  5. Execute the SQL script and verify that it ran successfully. For this, click Query and then click Execute
  6. If needed, do this for each Project Server database that you have - this directly correlates with the number of PWA instances that you have running.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [draft].[MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES]
(
    @PROJ_UID [UID],
    @DELETED_DATE [datetime],
    @DELETED_REV_COUNTER [int]
)
AS
BEGIN
   DECLARE @resUids table
   (
      RES_UID uniqueidentifier,
      PRIMARY KEY CLUSTERED(RES_UID)
   )
   INSERT INTO @resUids
   SELECT RES_UID FROM draft.MSP_PROJECT_RESOURCES res
   WHERE res.RES_UID NOT IN (SELECT DISTINCT RES_UID FROM draft.MSP_ASSIGNMENTS WHERE PROJ_UID = @PROJ_UID) AND
       res.PROJ_UID = @PROJ_UID AND res.MOD_REV_COUNTER < @DELETED_REV_COUNTER
   INSERT INTO draft.MSP_PROJECT_RESOURCES_SHADOW (RES_UID, PROJ_UID, DELETED_REV_COUNTER, DELETED_DATE)
      SELECT res.RES_UID, @PROJ_UID, @DELETED_REV_COUNTER, @DELETED_DATE
      FROM draft.MSP_PROJECT_RESOURCES res WITH (NOLOCK)
         LEFT JOIN draft.MSP_PROJECT_RESOURCES_SHADOW shadow
            ON (shadow.PROJ_UID = res.PROJ_UID
                  AND shadow.RES_UID = res.RES_UID
                  AND shadow.DELETED_REV_COUNTER = @DELETED_REV_COUNTER)
      WHERE res.RES_UID IN (SELECT RES_UID FROM @resUids) AND shadow.RES_UID IS NULL
   DELETE FROM draft.MSP_PROJECT_RESOURCES
   WHERE PROJ_UID = @PROJ_UID and RES_UID IN (SELECT RES_UID FROM @resUids)
END
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [pub].[MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES]
(
    @PROJ_UID [UID],
    @DELETED_DATE [datetime],
    @DELETED_REV_COUNTER [int]
)
AS
BEGIN
   DECLARE @resUids table
   (
      RES_UID uniqueidentifier,
      PRIMARY KEY CLUSTERED(RES_UID)
   )
   INSERT INTO @resUids
   SELECT RES_UID FROM pub.MSP_PROJECT_RESOURCES res
   WHERE res.RES_UID NOT IN (SELECT DISTINCT RES_UID FROM pub.MSP_ASSIGNMENTS WHERE PROJ_UID = @PROJ_UID) AND
       res.PROJ_UID = @PROJ_UID AND res.MOD_REV_COUNTER < @DELETED_REV_COUNTER
   INSERT INTO pub.MSP_PROJECT_RESOURCES_SHADOW (RES_UID, PROJ_UID, DELETED_REV_COUNTER, DELETED_DATE)
      SELECT res.RES_UID, @PROJ_UID, @DELETED_REV_COUNTER, @DELETED_DATE
      FROM pub.MSP_PROJECT_RESOURCES res WITH (NOLOCK)
         LEFT JOIN pub.MSP_PROJECT_RESOURCES_SHADOW shadow
            ON (shadow.PROJ_UID = res.PROJ_UID
                  AND shadow.RES_UID = res.RES_UID
                  AND shadow.DELETED_REV_COUNTER = @DELETED_REV_COUNTER)
      WHERE res.RES_UID IN (SELECT RES_UID FROM @resUids) AND shadow.RES_UID IS NULL
   DELETE FROM pub.MSP_PROJECT_RESOURCES
   WHERE PROJ_UID = @PROJ_UID and RES_UID IN (SELECT RES_UID FROM @resUids)
END
GO



Detection script to identify affected Project plans

This is an optional section that customers can use to determine if any of their Project plans have been impacted by the issue described in the above blog post. If a recent administrative backup of the Project plan is available, often times the administrative restore would be the best option to take. If you have further questions on recovery options, do open a support incident (no charges for cases related to this issue) so we can further assist. 


Note This script is intended to be run on the Project Server database and the steps for doing it is similar to the instructions in the above section.


SELECT DISTINCT A.PROJ_UID, P.PROJ_NAME
FROM DRAFT.MSP_ASSIGNMENTS A
LEFT JOIN DRAFT.MSP_PROJECT_RESOURCES PR ON A.RES_UID = PR.RES_UID AND A.PROJ_UID = PR.PROJ_UID 
INNER JOIN PUB.MSP_RESOURCES ER ON A.RES_UID = ER.RES_UID 
INNER JOIN DRAFT.MSP_PROJECTS P ON A.PROJ_UID = P.PROJ_UID
WHERE PR.RES_UID IS NULL
UNION
SELECT DISTINCT C.PROJ_UID, P.PROJ_NAME
FROM DRAFT.MSP_PROJECT_CALENDARS C
LEFT JOIN DRAFT.MSP_PROJECT_RESOURCES PR ON C.CAL_UID = PR.RES_UID AND C.PROJ_UID = PR.PROJ_UID 
INNER JOIN PUB.MSP_RESOURCES ER ON C.CAL_UID = ER.RES_UID 
INNER JOIN DRAFT.MSP_PROJECTS P ON C.PROJ_UID = P.PROJ_UID
WHERE PR.RES_UID IS NULL
ORDER BY P.PROJ_NAME

Properties

Article ID: 2933444 - Last Review: February 19, 2014 - Revision: 12.0
Applies to
  • Microsoft Project Server 2013
Keywords: 
KB2933444

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com