locked
Project Server 2010 : Webapp - tasks - "An Unknown Error has occured" RRS feed

  • Question

  • Software used

    • Project server 2010
    • SharePoint 2010 RTM
    • Sql Server 2008 R2

    Steps taken

    • new project server 2010 setup on a virtual machine
    • importing/publishing 2 projects from projectcentral 2000 to project server 2010.
    • a user has 122 tasks (in project central 2000 he only gets 15 tasks in his view)
    • unable to view the detail of the tasks --> unkown error.

    there is a sql time out in the log files, from the following query  (when i run it in SQL management studio it takes > 5 minutes)

    ################

    declare @ResUid UniqueIdentifier; set @ResUid = '9ffd71a7-c94a-44f5-b3b1-6d27eb17f541';
    declare @ViewUid UniqueIdentifier; set @ViewUid = 'a20480ef-c755-4da7-a36f-5fe92fed0c68'; 
    declare @P0 UniqueIdentifier; set @P0 = '9ffd71a7-c94a-44f5-b3b1-6d27eb17f541'; 

    SET NOCOUNT ON
      
    SELECT      MAS.ASSN_UID ,      MAS.PROJ_UID   INTO #T0 
    FROM dbo.MSP_ASSIGNMENTS_SAVED AS MAS    
          INNER JOIN dbo.MSP_PROJECTS AS MP ON MP.PROJ_UID = MAS.PROJ_UID    
          INNER JOIN dbo.MSP_TASKS_SAVED AS T ON T.PROJ_UID = MAS.PROJ_UID AND T.TASK_UID = MAS.TASK_UID    
          INNER JOIN dbo.MSP_PROJECT_RESOURCES AS R ON R.PROJ_UID = MAS.PROJ_UID AND R.RES_UID = MAS.RES_UID    
          INNER JOIN dbo.MSP_RESOURCES AS R2 ON R2.RES_UID = R.RES_DEF_ASSN_OWNER    
          LEFT JOIN dbo.MSP_RESOURCES AS R3 ON R3.RES_UID = MAS.RES_UID_OWNER    
          LEFT JOIN dbo.MSP_RESOURCES AS R4 ON R4.RES_UID = MAS.WRES_UID_MANAGER 
    WHERE MAS.WASSN_LOCKDOWN_BY_MANAGER = 0AND T.TASK_IS_ACTIVE = 1 AND ( (MAS.RES_UID_OWNER = @P0) )  
    CREATE CLUSTERED INDEX PK_#T0 ON #T0 (ASSN_UID, PROJ_UID)  
          
    SET NOCOUNT OFF  
           
    SELECT      MAS.ASSN_UID ,      MAS.PROJ_UID ,      MAS.TASK_NAME ,      MAS.ASSN_START_DATE ,      MAS.ASSN_FINISH_DATE ,      ASSN_REM_WORK ,      MAS.ASSN_PCT_WORK_COMPLETE ,      ASSN_WORK ,      ASSN_ACT_WORK ,      MAS.TASK_UID ,      PROJ_NAME ,      WASSN_IS_NEW_ASSN ,      MAS.WASSN_REMOVED_BY_RES ,      case when exists ( select * from MSP_ASSIGNMENT_TRANSACTIONS t where MAS.ASSN_UID = t.ASSN_UID and t.ASSN_TRANS_STATE_ENUM = 1) then 1 else 0 end AS ASSN_RESPONSE_PENDING ,      MAS.WASSN_MGR_UPDATED ,      MAS.WASSN_ACTUALS_PENDING ,      MAS.WASSN_SEND_UPDATE_NEEDED ,      MAS.WASSN_LOCKDOWN_BY_MANAGER ,      MAS.WASSN_DELETED_IN_PROJ ,      MAS.WASSN_COMMENTS ,      R.RES_NAME ,      T.TASK_IS_MILESTONE ,      WASSN_UPDATE_TRACKING_MODE ,      T.TASK_NOTES ,      T.TASK_IS_MANUAL ,      T.TASK_HIERARCHY ,      T.TASK_DUR_TXT ,      T.TASK_FINISH_TXT ,      T.TASK_START_TXT ,      MAS.TASK_IS_SUMMARY ,      WO.WOBJ_UID ,      MP.PROJ_OPT_MINUTES_PER_DAY ,      MP.PROJ_OPT_MINUTES_PER_WEEK ,      MP.PROJ_OPT_DAYS_PER_MONTH ,      MP.PROJ_OPT_CURRENCY_CODE ,      MAS.ASSN_STOP_DATE ,      T.TASK_IS_SUBPROJ ,      T.TASK_IS_ACTIVE ,      T.TASK_START_DATE ,      T.TASK_FINISH_DATE ,      T.TASK_DUR ,      MAS.RES_UID ,      R.RES_MATERIAL_LABEL ,      R.RES_TYPE ,      ASSN_UPDATE_NEEDED  
    FROM dbo.MSP_ASSIGNMENTS_SAVED AS MAS     
        INNER JOIN dbo.MSP_PROJECTS AS MP ON MP.PROJ_UID = MAS.PROJ_UID    
        INNER JOIN dbo.MSP_TASKS_SAVED AS T ON T.PROJ_UID = MAS.PROJ_UID AND T.TASK_UID = MAS.TASK_UID    
        INNER JOIN dbo.MSP_PROJECT_RESOURCES AS R ON R.PROJ_UID = MAS.PROJ_UID AND R.RES_UID = MAS.RES_UID    
        INNER JOIN dbo.MSP_RESOURCES AS R2 ON R2.RES_UID = R.RES_DEF_ASSN_OWNER    
        LEFT JOIN dbo.MSP_RESOURCES AS R3 ON R3.RES_UID = MAS.RES_UID_OWNER    
        LEFT JOIN dbo.MSP_RESOURCES AS R4 ON R4.RES_UID = MAS.WRES_UID_MANAGER   
        INNER JOIN #T0 AS keys ON keys.ASSN_UID = MAS.ASSN_UID AND keys.PROJ_UID = MAS.PROJ_UID    
        LEFT JOIN dbo.MSP_WEB_OBJECTS AS WO ON WO.WOBJ_PROJ_UID = T.PROJ_UID AND WO.WOBJ_TASK_UID = T.TASK_PUBLISHED_UID AND WO.WOBJ_TYPE = 2 
    WHERE MAS.WASSN_LOCKDOWN_BY_MANAGER = 0AND T.TASK_IS_ACTIVE = 1   
             
    DROP TABLE #T0;

    #################

     

    This query creates a tempory table -- when ran seperately = fast

    The query does a selection joining the temporary table.

    When running the second query without the join on the temp it takes 8 seconds and result in 826 records (all assignements in the db)

    After looking at the join and where clauses used in the first and second query i see they are almost identical and with only adding "AND ( (MAS.RES_UID_OWNER = @P0) ) " and not using a temp table, I get the wanted result (122) only taking half a second.

    So

    Suggestions how to fix, circumvent the problem?

     

     

     

    Wednesday, June 23, 2010 1:18 PM

Answers

All replies

  • Please open an incident with Microsoft Support Orgnization (Microsoft Premier).
    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Thursday, July 1, 2010 2:56 AM
  • please is there any info about this case?
    Thursday, April 7, 2011 3:08 PM
  • Exact same problem, took me hours to trace it, but i get the exact same behavior, some people can open the tasks view normally, some other people get timeouts.

    Tried multiple browsers, logging different users on different machines, different hours in the day with different loads and capacities, verified that SQL Server and SharePoint Servers and Project Servers are not over utilitized.

    There just seems to be something wrong with this query, i tried running it in SQL Server Management Studio, and it does indeed take alot of time to generate results, about 5 mins or so for some users.

    Please can anyone suggest a solution other than contacting support, this is heavily impacting our business.

    Sunday, October 2, 2011 12:42 PM
  • Hi there,

    Try updating the published database stats, well do all four Project Server databases using this command against each database

    EXEC SP_updatestats

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Sunday, October 2, 2011 4:00 PM
  • Thanks Paul for the reply, will try your solution and give you feedback shortly.
    Monday, October 3, 2011 9:35 AM
  • Dear Paul,

     

    Thank you sincerely, your solution worked like a charm.

    Apparently the problem is that we have added so many projects in such a short time. which lead to this issue.

    The stored procedure SP_udpdatestats created the necessary indexes in the databases to help enhance the performance.

    The query which used to take 6 mins now takes 3 seconds :)

    Thank you :)

    Monday, October 3, 2011 10:22 AM
  • Thanks Paul.


    pankaj keshari Accenture

    Monday, August 4, 2014 9:50 AM