Thursday, January 19, 2012

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.   

14 comments:

  1. Hello Piet,

    I'm experiencing the same problem in our production environment. I just found out that multiple assigned custom fields seem to be the problem in our case. The error message always appears in the log when you select a view in the project center (pwa) which uses a custom field to filter the project list. You can find the affected projects with the following TSQL-script (please replace [ProjectServer_Published] with the name of your project server published database) :

    SELECT
    (SELECT PRJ.PROJ_NAME FROM ProjectServer_Published.dbo.MSP_PROJECTS PRJ WHERE pcf.PROJ_UID = PRJ.PROJ_UID) as Projectname
    ,(SELECT PCFV.[MD_PROP_NAME] FROM [ProjectServer_Published].[dbo].[MSP_CUSTOM_FIELDS] PCFV WHERE PCFV.[MD_PROP_UID] = pcf.MD_PROP_UID) as CustomFieldName
    ,count(*)
    FROM [ProjectServer_Published].[dbo].[MSP_PROJ_CUSTOM_FIELD_VALUES] pcf
    GROUP BY PROJ_UID, MD_PROP_UID
    HAVING COUNT(*) > 1
    order by ProjectName, CustomFieldName

    If you open one of the affected projects in pwa and view the projects detail page, you should see that custom fields for this project, which should only contain one value, now contain a list of values separated by comma. Strangely enough, it seems, that 4 entries is the maximum number of entries displayed on the project detail page.

    At the moment we solve the problem by deleting the published version of the project and publishing the draft version again.

    At this time, this problem seems to be unknown for Microsoft, as a case, opened at our project partner returned with no result at the moment.

    I hope, these information help to analyse the problem in your environment and I would appreciate hearing from you

    kind regards

    Jan

    ReplyDelete
    Replies
    1. Great to hear from a fellow Project Server colleague! Good pick-up Jan. I suspected it was something to do with the custom fields but would love to get to the bottom of understanding why this even occurs. I am currently in the process of logging a call with Microsoft on behalf of our client so will be updating the blog once we get more informtion on this issue. I have also seen this issue occur for another client of ours who started with a clean installation on Project Serverf 2010 (No migration). Current client was a migration from 2003 to 2007, then to 2010. We have had the same issue occur four times in the last two weeks so our client is wanting to escalate this issue now.

      Big thanks for sharing this information with the community! Keep us updated if you hear anything.

      Regards,
      Piet Remen

      Delete
    2. Hi Jan,

      I also have a customer with this exact problem. I used the SQL script to discover more than 1 project with multiple values selected for single-value enterprise custom fields. These projects would have also crashed the Project Center views if the view filter was processing against the errant enterprise custom fields.

      I suspect some form of local cache corruption of the Enterprise Global settings for MS Project Pro. I recommend Microsoft Project Professional users kill the local cache folders.

      I also strongly recommend customers upgrade the MS Project Pro CU levels to at least October 2011.

      Thanks,
      Russ Young, QuantumPM

      Delete
    3. Hi Jan,

      We also have this problem. I wrote a similar script which takes into account multi select lookup tables. I thought it might be helpful...


      Select ProjectName,MD_Prop_Name,TheValue,Count(*) TheCount From
      (
      Select ProjectName,MD_Prop_Name
      ,Case When FLAG_VALUE Is Not Null Then Cast(FLAG_VALUE as Varchar(50))
      When NUM_VALUE Is Not Null Then Cast(NUM_VALUE as Varchar(50))
      When DATE_VALUE Is Not Null Then Cast(DATE_VALUE as Varchar(50))
      When TEXT_VALUE Is Not Null Then TEXT_VALUE
      When lt_Value_Text Is Not Null Then lt_Value_Text
      ELse Null End TheValue
      From ProjectServer_Published.dbo.MSP_EpmProject_UserView as uv
      Left Join ProjectServer_Published.dbo.MSP_PROJ_CUSTOM_FIELD_VALUES pc on pc.Proj_UID = uv.ProjectUID
      Left Join ProjectServer_Published.dbo.MSP_CUSTOM_FIELDS cf on cf.MD_PROP_ID = pc.MD_PROP_ID
      Left Join ProjectServer_Published.dbo.MSP_LOOKUP_TABLE_VALUES lt on lt.LT_Struct_UID = CODE_VALUE
      ) as q
      Group by ProjectName,MD_Prop_Name,TheValue Having Count(*) > 1

      Delete

    4. outlook 2010 wholesale key , windows 10 kms activation server 2008 r2 , windows 10 serial keys product key , windows 10 activation update , windows 7 unlimate product key , clé d'activation norton internet security 2012 invalide , buy windows 10 product key , office 2016 product key , ljDeg6

      windows server 2012 r2 buy

      office 2013 key sale

      cheap rosetta stone french

      Delete
  2. Hello Piet,

    I would really appreciate hearing from you, as finding someone who has the same problem we experience would make it a lot easier to get the problem fixed by microsoft.

    kind regards
    Jan

    ReplyDelete
  3. We have just encountered this issue. This was at our first production instance to get December 2011 CU and we haven't seen this before (for many server instances and many thousands of projects.)

    Same symptoms. Filtered view stopped working. Removing the filter from the view gets things working again. ULS log contains the SQL query failing. This shows two projects with multiple Custom field values assigned when they should only have one.

    Running this query on the Published DB will remove the remembered view for a user, freeing them up to select other views, as long as the default view doesn't have the problem.


    DELETE FROM
    dbo.MSP_WEB_USERPROPERTIES
    WHERE RES_UID = 'Insert Resource UID here'
    AND PROP_NAME = 'ViewUidProjectCenterJSGridControl'

    We'll likely be opening an issue with MS soon.

    ReplyDelete
  4. Hi, I have a client with the same problem, did you find the cause for this happening?

    I have already came across this problem and resolved it by the elimination of the projects from the published data base, but after a week the problem arise again.

    Thanks in advance

    ReplyDelete
    Replies
    1. I received an update last week from Microsoft Premier Support advising the issue is still being worked on. Still no update sorry in regards to when we can expect to get the hotfix but as soon as I have a date I will update the Blog post.

      Regards,
      Piet Remen

      Delete
    2. Please refer to the UPDATE section of the blog which now shows a link on where to get the fix for this issue.

      Delete
    3. Good day
      Just want to know if your “An unknown error has occurred" was resolved
      We also had it and it was resolved on our side
      Adri

      Delete
  5. LJB, your query solved my problem. Tks

    ReplyDelete
  6. Excellent post. I was checking constantly this blog and
    I'm impressed! Extremely helpful info specifically the last part :
    ) I care for such info a lot. I was looking for this certain info for a very
    long time. Thank you and best of luck.

    My weblog; at home colon cleanse

    ReplyDelete