You are currently offline, waiting for your internet to reconnect

Steps to apply updated stored procedures for Project Server 2013

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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [draft].[MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES](    @PROJ_UID [UID],    @DELETED_DATE [datetime],    @DELETED_REV_COUNTER [int])ASBEGIN   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)ENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [pub].[MSP_WINPROJ_DELETE_OLD_PROJECT_RESOURCES](    @PROJ_UID [UID],    @DELETED_DATE [datetime],    @DELETED_REV_COUNTER [int])ASBEGIN   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)ENDGO



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_NAMEFROM DRAFT.MSP_ASSIGNMENTS ALEFT 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_UIDWHERE PR.RES_UID IS NULLUNIONSELECT DISTINCT C.PROJ_UID, P.PROJ_NAMEFROM DRAFT.MSP_PROJECT_CALENDARS CLEFT 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_UIDWHERE PR.RES_UID IS NULLORDER BY P.PROJ_NAME

Properties

Article ID: 2933444 - Last Review: 02/19/2014 08:01:00 - Revision: 12.0

Microsoft Project Server 2013

  • KB2933444
Feedback
/html>:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">