Project Centre – “An unknown error has occurred”

UPDATE
Microsoft have officially relased a fix for this particular issue. Please refer to Brian Smiths post for more details: http://blogs.msdn.com/b/brismith/archive/2012/04/16/project-server-2010-an-unknown-error-in-project-center-usually-related-to-filters-and-duplicate-custom-field-values.aspx

Download the fix from the following link to the Feb 2012 Cumulative Update: http://support.microsoft.com/kb/2598251

This issue was raised directly to Microsoft. The last formal communcation I have recieved on this issue is that it has been ackowledged by Microsoft now as a 'known issue'. The current status is that a hot fix is scheduled for release in the April 2012 Cumulative Updates. If you would like to reference our support call in this instance to fast track the process then please quote SR 112020314749718 in your support call.

ISSUE

When navigating from PWA to the Project Centre, the user is presented with an error message explaining ‘An unknown error has occurred’. This only occurs for some certain views in the project centre but it does affect all users of the Project Server instance. Since Project Server remembers the last view a user has selected this becomes problematic as once the broken view becomes the users default view, the next time they navigate to the Project Centre, the view selector on the ribbon bar becomes disabled and disallows them to select an alternative view, therefore completely losing the ability to view the Project Centre all together. Over the last two weeks this incident has occurred four times. We have a work around solution for resolving it but request assistance to ensure this issue does not continue to disrupt our business.

STEPS TO REPRODUCE THE ISSUE

Unfortunately this is still a mystery. One minute the Project Centre is fine, then someone saves/publishes their project schedule and the Project Centre view breaks. We then need to go through the motion of working out which project schedule has caused the issue and rectify it.

RESOLUTION STEPS.

1. Note your most recently published projects around the time the issue begun to identify a potential list of suspect project schedules.
2. Perform an administrative backup of project via PWA>Server Settings>Administrative Backup. This will give you something to restore to if the project is not the culprit.
3. Now if you are not very techy then you can go through the process of elimination. For each project use the administrative restore functionality to restore the project schedule. Then open the restored project schedule in project professional and publish the project.. Test the Project Centre. Continue this process until the Project Centre is working again and you can therefore identify the problematic project. In this particular scenario,  we found the project, restored to an old version and got it working. Then we restored the most recent version again, re-published the project and everything was still working and the PM did not lose any data. Phew!
   UPDATE(20 Feb 2012) - In some instances we also had to delete the project from the publish database before we performed the restore. Be careful though as you may loose some values for any lookup type customfields in the schedule.
4. Now for those who like to get down and dirty into the techy side of things, there is something you can do to hunt down the project. To achieve this you will need to navigate to the application server running the Project Server service and take a look at the SharePoint Log files. In this instance I always use the ULSViewer to reduce the pain in viewing these logs. It allows you to open the ULS logs at a given point in time, then you reproduce the error and stop the logger so you only have to read through a short list of logs. However if you reproduce the error and then quickly open the most recent log file you should be able to still figure it out.
5. I discovered one log entry in particular which was logged as an ‘Exception’ level error. I’ll post the error at then end of the post for the search bots but it basically logged a large chunk of SQL. I copied and pasted this SQL section of the error into SQL Management Studio and trimmed of any exception text messages. I had to wrap single quotes around the initial GUID parameter declaration section and point the query at the Project Server Published Database to get the query to work.
6. Once you get the data out, look for any duplicate project name (PROJ_NAME) rows or duplicate rows of PROJ_UIDS. You can use excel to help out with this task. I performed a sort by PROJ_UID and then added an excel formula which checks if the cell on the same row is the same as the one below it. If it is return the text "DUPLICATE". If you need to figure out what Project name maps to the duplicate PROJ_UID then take a look at the MSP_Projects table in the Published Database. (e.g. SELECT * FROM MSP_PROJECTS WHERE PROJ_UID = ‘Insert GUID here’)
7. Once you find the little gremlin, Apply the same logic as described in step 3 to restore the previous schedule, remembering to publish the schedule after it has been restored. Once confirmed the project centre is working again, restore the most recent schedule, republish and life should be normal again. If you do not have administrative backups on…then learn from this lesson and start doing them. You can then try deleting the project schedule tasks to try and eliminate the tasks/s causing the row duplication.

Related ‘Exception’ error located in the SharePoint Logs:
Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Project.ProjectGetProjectCenterProjectsForGridJson Microsoft.Office.Project.Server.DataAccessLayer.FilterDal+FilterException: Error during filter query execution.   Query:     declare @ResUid UniqueIdentifier; set @ResUid = 79f96144-b782-4504-b44b-cd8a0b7385b3;  declare @PermUid UniqueIdentifier; set @PermUid = a120a079-75bc-4f0f-b376-3fb0ae9ac940;  declare @ViewUid UniqueIdentifier; set @ViewUid = 6ac38e5a-af43-4ac4-9e5e-63178560ba5e;  declare @P0 UniqueIdentifier; set @P0 = 998c2ef5-b986-4d10-8d36-f5da091a5956;  declare @P1 UniqueIdentifier; set @P1 = 18a812d6-e266-496c-a133-4e8a98ac4822;  declare @P2 UniqueIdentifier; set @P2 = a849eb95-6e4c-4ed6-891d-1e737b7b0cf3;  declare @P3 UniqueIdentifier; set @P3 = 8c832669-2f8e-474d-9a91-a581ec28a48b;  declare @P4 Int; set @P4 = 5;  declare @P5 UniqueIdentifier; set @P5 = a849eb95-6e4c-4ed6-891d-1e737b7b0cf3;    SET NOCOUNT ON    SELECT      T.PROJ_UID   INTO #T0   FROM dbo.MSP_PROJECTS AS P      INNER JOIN dbo.MSP_TASKS AS T ON T.PROJ_UID = P.PROJ_UID     INNER JOIN dbo.MSP_RESOURCES AS R ON R.RES_UID = P.WRES_UID     LEFT JOIN dbo.MSP_WORKFLOW_STATUS AS WFSTS ON WFSTS.PROJ_UID = P.PROJ_UID     INNER JOIN dbo.MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID(@ResUid, @PermUid, @ViewUid, 3) AS perm ON perm.PROJ_UID = T.PROJ_UID     LEFT JOIN dbo.ProjectSummaryCustomFields AS TABLEALIAS_0 ON TABLEALIAS_0.PROJ_UID = P.PROJ_UID AND TABLEALIAS_0.MD_PROP_UID = @P0     LEFT JOIN dbo.ProjectSummaryCustomFields AS TABLEALIAS_1 ON TABLEALIAS_1.PROJ_UID = P.PROJ_UID AND TABLEALIAS_1.MD_PROP_UID = @P2  WHERE T.TASK_OPTINDX = 1.0    AND (ISNULL(WFSTS.STAGE_STATUS,-1) IN (-1, 1,2,3,5,6))   AND  (  ( (TABLEALIAS_0.CODE_VALUE = @P1) AND ((TABLEALIAS_1.CODE_VALUE <> @P3) OR (TABLEALIAS_1.CODE_VALUE IS NULL)) )  AND ((P.PROJ_TYPE <> @P4) OR (P.PROJ_TYPE IS NULL)) )   CREATE CLUSTERED INDEX PK_#T0 ON #T0 (PROJ_UID)    SET NOCOUNT OFF    SELECT      T.PROJ_UID ,      P.PROJ_NAME ,      R.RES_NAME ,      T.TASK_START_DATE ,      T.TASK_FINISH_DATE ,      TB0.TB_BASE_FINISH AS TB_BASE_FINISH_0 ,      T.TASK_PCT_COMP ,      TB0.TB_BASE_COST AS TB_BASE_COST_0 ,      T.TASK_COST ,      T.TASK_REM_COST ,      P.PROJ_LAST_SAVED ,      P.WPROJ_LAST_PUB ,      P.PROJ_OPT_MINUTES_PER_DAY ,      P.PROJ_OPT_MINUTES_PER_WEEK ,      P.PROJ_OPT_DAYS_PER_MONTH ,      T.TASK_SUMMARY_PROGRESS_DATE ,      T.TASK_IS_MILESTONE ,      dbo.MSP_FN_HYPERLINK_HREF(T.TASK_HYPERLINK_ADDRESS, T.TASK_HYPERLINK_SUB_ADDRESS) AS TASK_HYPERLINK_HREF ,      T.TASK_OUTLINE_LEVEL ,      P.PROJ_TYPE ,      T.TASK_DUR_FMT ,      P.WSTS_SERVER_UID ,      P.PROJ_OPT_CURRENCY_CODE ,      P.PROJ_ACTIVE_RISK_COUNT ,      P.PROJ_ACTIVE_ISSUE_COUNT ,      P.PROJ_TOTAL_DOC_COUNT ,      WOB.WOBJ_ISSUE_REF_CNT ,      WOB.WOBJ_RISK_REF_CNT ,      WOB.WOBJ_DOC_REF_CNT ,      PJSYNC.SYNC_WSS_LIST_UID ,      TB0.TB_BASE_START AS TB_BASE_START_0 ,      T.TASK_COMPLETE_THROUGH ,      T.TASK_IS_SUMMARY ,      (CASE WHEN T.TASK_UID=T.TASK_PARENT_UID THEN 1 ELSE 0 END) AS TASK_IS_PROJECT_SUMMARY ,      T.TASK_IS_ACTIVE ,      T.TASK_DUR   FROM dbo.MSP_PROJECTS AS P      INNER JOIN dbo.MSP_TASKS AS T ON T.PROJ_UID = P.PROJ_UID     INNER JOIN dbo.MSP_RESOURCES AS R ON R.RES_UID = P.WRES_UID     LEFT JOIN dbo.MSP_WORKFLOW_STATUS AS WFSTS ON WFSTS.PROJ_UID = P.PROJ_UID     INNER JOIN #T0 AS keys ON keys.PROJ_UID = P.PROJ_UID     LEFT JOIN dbo.MSP_TASK_BASELINES AS TB0 ON TB0.PROJ_UID = T.PROJ_UID AND TB0.TASK_UID = T.TASK_UID AND TB0.TB_BASE_NUM = 0     LEFT JOIN (SELECT WOBJ_PROJ_UID as PROJ_UID,[4] as WOBJ_ISSUE_REF_CNT, [5] as WOBJ_RISK_REF_CNT, [3] as WOBJ_DOC_REF_CNT               FROM (               SELECT WOBJ_TYPE, WOBJ_PROJ_UID                FROM MSP_WEB_OBJECTS WHERE WOBJ_TASK_UID='00000000-0000-0000-0000-000000000000') pwob              PIVOT (COUNT(WOBJ_TYPE) FOR WOBJ_TYPE in([3] ,[4],[5])) AS pvt) AS WOB ON WOB.PROJ_UID = P.PROJ_UID     LEFT JOIN dbo.MSP_SYNC_PROJECT_SETTINGS AS PJSYNC ON PJSYNC.PROJ_UID = P.PROJ_UID  WHERE T.TASK_OPTINDX = 1.0    AND (ISNULL(WFSTS.STAGE_STATUS,-1) IN (-1, 1,2,3,5,6))      CREATE TABLE #T1 (MD_PROP_UID uniqueidentifier)   INSERT INTO #T1 (MD_PROP_UID) VALUES (@P5)   INSERT INTO #T1 (MD_PROP_UID)      SELECT MD_PROP_UID_SECONDARY FROM MSP_CUSTOM_FIELDS WHERE MD_PROP_UID IN (@P5)   INSERT INTO #T1 (MD_PROP_UID)      SELECT DISTINCT MD_PROP_UID FROM MSP_PROJECT_CUSTOM_FIELDS_WORKING_VIEW WHERE MD_PROP_UID_SECONDARY IN (@P5)   INSERT INTO #T1 (MD_PROP_UID)      SELECT DISTINCT MD_PROP_UID_SECONDARY FROM MSP_PROJECT_CUSTOM_FIELDS WHERE MD_PROP_UID IN (@P5)     SELECT      CFV.CUSTOM_FIELD_UID ,      CFV.PROJ_UID ,      CFV.MD_PROP_UID ,      CFV.MD_PROP_ID ,      CF.MD_PROP_NAME ,      CFV.FIELD_TYPE_ENUM ,      CFV.FLAG_VALUE ,      CFV.DUR_VALUE ,      CFV.NUM_VALUE ,      CFV.DUR_FMT ,      CFV.DATE_VALUE ,      CFV.CODE_VALUE ,      CFV.TEXT_VALUE ,      CFV.INDICATOR_VALUE   FROM dbo.MSP_PROJ_CUSTOM_FIELD_VALUES AS CFV      INNER JOIN #T0 AS keys ON keys.PROJ_UID = CFV.PROJ_UID     INNER JOIN dbo.#T1 AS cfkeys ON cfkeys.MD_PROP_UID = CFV.MD_PROP_UID     INNER JOIN dbo.MSP_CUSTOM_FIELDS AS CF ON CF.MD_PROP_UID = CFV.MD_PROP_UID  UNION  SELECT      CFV.CUSTOM_FIELD_UID ,      CFV.PROJ_UID ,      CFV.MD_PROP_UID ,      CFV.MD_PROP_ID ,      PCF.MD_PROP_NAME ,      CFV.FIELD_TYPE_ENUM ,      CFV.FLAG_VALUE ,      CFV.DUR_VALUE ,      CFV.NUM_VALUE ,      CFV.DUR_FMT ,      CFV.DATE_VALUE ,      CFV.CODE_VALUE ,      CFV.TEXT_VALUE ,      CFV.INDICATOR_VALUE   FROM dbo.MSP_TASK_CUSTOM_FIELD_VALUES AS CFV      INNER JOIN dbo.MSP_TASKS AS T ON T.PROJ_UID = CFV.PROJ_UID AND T.TASK_UID = CFV.TASK_UID AND T.TASK_UID = T.TASK_PARENT_UID     INNER JOIN #T0 AS keys ON keys.PROJ_UID = CFV.PROJ_UID     INNER JOIN dbo.#T1 AS cfkeys ON cfkeys.MD_PROP_UID = CFV.MD_PROP_UID     INNER JOIN dbo.MSP_PROJECT_CUSTOM_FIELDS AS PCF ON PCF.MD_PROP_UID = CFV.MD_PROP_UID AND PCF.PROJ_UID = CFV.PROJ_UID    DROP TABLE #T0;  DROP TABLE #T1;     ---> System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.