Project Center Error: Unknown Error has occurred

The Project Center page is failing to load, with the error “Unknown Error has occurred”. All the other pages and components are working fine, the Project Sites, PDP pages, Project publish and save, timer jobs etc.

The error that is logged in the ULS logs on the Application server (usadc-vsrpmp04) says:

 

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 = 147d154f-7a41-4601-8914-7d107129ff06; declare @PermUid UniqueIdentifier; set @PermUid =

<…really long SQL Query …> ; ---> System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

 

After ruling out the issues with Code or SQL Connection issues, we figured it is a data issue, because all the other components are working fine except the Project Center Grid. The Project center Grid and the views on it are rejecting the retrieved data from SQL. The data problem can be duplicate rows, NULL values, the ULS error said "One or more rows contain values violating non-null, unique, or foreign-key constraints". The problem can also be the data in the custom fields that might be causing the problem.

By adding filters on the Project center Views on Project creation date and modified date, we traced it on one specific Project that is failing the grid. When this project is filtered, Project Center grid is loading fine. We noticed this project has lots of issues, most of the key data required to create this project is missing, also the Project title is not the Project code but some descriptive text. The user might have used Project Pro and deleted the key data and changed the Project title and published the project.

 

On hindsight we figured we could have run the following query against the Published database, to get the list of projects that are modified today to track down the offending project.

SELECT *

 FROM  MSP_PROJECTS

 WHERE projectserver_reporting.dbo.MSP_Q_DateOnly(MOD_DATE) = projectserver_reporting.dbo.MSP_Q_DateOnly(CAST('03/16/2012' AS DATETIME))

 

To do:

1.       Contact MS and report this issue about the Project Center Grid and how bad data on one Project fails the entire page. Get an ETA on a patch or CU.

2.       In future to avoid longer downtimes, have the SOC monitor the Project Center grid, not just the page and alert immediately.

3.       Put additional checks during Reporting Publishing event handler to avoid deleting required fields.

For future reference:

http://social.technet.microsoft.com/Forums/ta/projectserver2010general/thread/236336b7-bbcb-4db3-9483-e4dd940aaff5

 

**********************  IMPORTANT--PLEASE READ  ************************
This electronic message, including its attachments, is COMPANY CONFIDENTIAL
and may contain PROPRIETARY or LEGALLY PRIVILEGED information.  If you are
not the intended recipient, you are hereby notified that any use, disclosure,
copying, or distribution of this message or any of the information included
in it is unauthorized and strictly prohibited.  If you have received this
message in error, please immediately notify the sender by reply e-mail and
permanently delete this message and its attachments, along with any copies
thereof. Thank you.
************************************************************************