none
Project Center View Custom Error when Filtering on Custom Field Value RRS feed

  • Question

  • Hi, after applying SP1 and the October CU in our production environment we see one new strange issue with the views in the Project Center. Views that filter on certain custom fields and specific values fail, while others work perfectly. We have for example a field called 'Project Status' with an associated lookup table. If we set the filter on the view to 'Project Status equal Closed' it works fine, but if we set it to be 'Project Status does not equal Closed' the view fails with 'Unknown error'. The same goes for another custom field called 'Owning Entity'.

    We did manage to find to reduce the possible error situations to 'Project Status equal Open' (so like <> Closed) and when we filter for two specific entities with the Owning Entity field. This could indicate that there are projects, matching certain criteria, containing some invalid data that causes the view to crash. The two strange things is that we did not see this issue in our test environment that is an exact copy of live. We also do not see this issue if we do not put any filter on the view at all.

    Does anyone have some ideas on where to look for the issue and if related to specific projects how we could identify those projects?


    Regards, Stig
    Monday, December 5, 2011 3:37 PM

Answers

All replies

  • Hi Stig,

     

    The same is happing here!! Just with a custom field that get values from a Lookup table. When we do the Filter to "does not equals" to "xxxx", we receive the OK from the filter, but when we try to check the view from Project Center we receive the same error as you "unknown error".

    Any ideas??

    Kind regards,


    Miguel Soler
    Monday, December 5, 2011 11:00 PM
  • Hi Miguel, I am afraid not. We have found out that the issue actually lies with certain values. We get the same error when we try 'Project Status equal Open' as we get with 'Project Status does not equal Closed'. So we thought maybe it could be due to some data issues with a project that has status Open, but which it is we do not know. And we would then also expect and issue if we have no filter, since then that project would show.

    If we do not get any ideas to fix it from here we will log a call with Microsoft next week. When we have a resolution I will keep you updated.


    Regards, Stig
    Tuesday, December 6, 2011 12:32 PM
  • Hi Stig!

    Glad to know that. This issue is really strange and sounds dummy but we have a headache with that. Hope Microsoft can help you, and wait for your updates!

     

    Thanks!


    Miguel Soler
    Tuesday, December 6, 2011 1:02 PM
  • The issue has now been recognized by Microsoft and an engineer is working on trying to find the root cause of the issue. Basically the error in project center views are returned due to duplicate custom field records in the Published database. For some reason some projects gets duplicate records inserted on save/publish of the project. It is now being worked on identifying the exact cause of that.

    We did have a short-term solution by removing the duplicate records and then re-editing and saving the affected projects, but that turned at to be a very short-term solution as we see the same issue again this morning. So no real progress yet.


    Regards, Stig
    Wednesday, December 14, 2011 8:56 AM
  • Hi Stig,

     

    Thanks for your feedback! I´m waiting your news, and knowing that meanwhile I will try to do an Administrative Backup/Restore of Custom Fields in Project Server. In my case I have two environments, and I guess that some projects are corrupted and the error is becoming with...

    Thanks again!


    Miguel Soler
    Thursday, December 15, 2011 8:32 AM
  • Hi Miguel,

    you can always try that, but I doubt it has any effect. The backup/restore of custom fields will rebuild the reporting database, but as the problem here lies in the Draft and Published databases it is unlikely to alleviate the problem.

    PS! Make sure you have at least the August CU on the server before you start the backup/restore.


    Regards, Stig
    Thursday, December 15, 2011 9:38 AM
  • Hi Stig,

    We are stucked at this point. There was no result succesful (as you pointed) making the administrative backup/restore action.

    We already have the December CU.

    Any updates??

    Kind Regards,


    Miguel Soler
    Tuesday, January 3, 2012 2:15 PM
  • Hi Miguel,

    progress is slow. We just provided Microsoft with a copy of our databases for further analysis so no solution yet. They have identified that the issue is that on saving projects some will duplicate the custom field data. The project center views therefore fail as the queries behind them return duplicate rows. They do however still not know what the cause of the issue is so no fix available.

    Removing any filters on project center views should relieve the issue. This might work for some, but not for all as of course PWA remembers each user's settings and might on initial contact not use the updated view. Maybe removing the filters and then rebooting could resolve that, but I think not.


    Regards, Stig
    Tuesday, January 3, 2012 3:52 PM
  • Thanks for your answer Stig.

    I´ve removed the filter that´s causing the problem and everything became to the life, of course, but I have some other problems in this environment that was migrated from 2007, and as we are suffering, with some problems migrated too.

    We already have another case open with Microsoft since 2 months ago with no resolution yet...

    Hope they can help you, and with your resolution can help us.

    Best Regards!


    Miguel Soler
    Tuesday, January 3, 2012 4:04 PM
  • This week we have learned from Microsoft that this issue has indeed been before, in fact by multiple customers over the last 4 years, and that they have no clue what causes it.

    We have confirmed that even after fixing the issue once (deleting custom field values and re-creating) the problem comes back again later. Now we have selected 5 specific projects that we will follow to see which updates are made and whether that can in any way be related to the creation of the duplicate values. Needless to say this will take quite some time before we can expect any results. So do not expect any quick solution to this issue.


    Regards, Stig
    Wednesday, January 18, 2012 2:09 PM
  • Hi, I was thinking always would not be a quick solution, besides if Microsoft support has the issue identified from 4 years ago... OK, I didn´t try recreating the custom field values, but some days ago I was thinking in another workaround that, of course I have not tested it yet. You are talking about recreating values but, if you create another CF with another name, and then by PSI pass the values from the "wrong" CF to this new one? I have to test it, but try if you want. Best Regards!
    Miguel Soler
    Wednesday, January 18, 2012 6:22 PM
  • Hi, our re-creation of the field values is done manually by the project managers. We have not considered any development to resolve that. This mostly because even if we do there is no guarantee that it will resolve the issue. We see cases where we have deleted the custom field values where there are duplicates. Once the project manager saves it all then looks OK, but a couple of saves later we again have duplicate values.
    Regards, Stig
    Thursday, January 19, 2012 10:18 AM
  • Latest update from Microsoft today:

    First important topic: 

    Several Customers in EMEA and in US are facing this problem.

     

    Second important topic:
    Product Group is working on a fix, not sure this will be ready in the February CU

     

    Background of this seem to be that in October CU there was a Client fix which solves problems with the Shadow table which control the demand loading of information.

     


    Regards, Stig
    Friday, January 20, 2012 9:46 AM
  • Hi  Stig,

    This sound as always as a wall... Do you have this Microsoft´s update in your mail or did you found it in a web page?

    If there is a Microsft´s page telling that will be great for our customer...you know!

     

    Thanks in advance!


    Miguel Soler
    Friday, January 20, 2012 10:21 AM
  • This is an update to the case that we ourselves have logged with Microsoft. I am not aware of any Microsoft page that informs on the fixes they are working on until the fix package is released. If you need anything more I would guess that you or your customer would need to log a case with Microsoft.

    I think that it is a positive message as they are now indeed confirming that they know where the issue originates and are already working on a fix.


    Regards, Stig
    Friday, January 20, 2012 10:30 AM
  • Hi Stig and Miguel,

    I would like to add my observations in this issue (unfortunately not a solution) that affects so many users.

    1. I had this problem in 2007 and I am having the same problem in a fresh 2010 installation.
    2. In 2007 version I detected in some sites, enterprise field data corruption. I detected the projects with this problem (using additional filtering in Category), republished them and the embedded filter in the views came back to life. It took some time to find the projects with this problem. I did not find the origin of the problem, but the server suffered two incidents in SQL server: one disk out of space and one SQL statistic table rebuilt from scrath made by MS Premier Support and the issue were detected months later. So I am not sure if there is any relation.
    3. In 2010 version (not migrated from 2007, with SP1 and Dec 2011 CU) I detected this problem in pre-production phase. One solution that you mentioned is delete the embedded filter. The other one was to delete the projects (I did not have so many to test) until it restarted to work, assuming that one of them has a enterprise field content corrupted.
    4. The last observation is that if I delete the embedded filter in the Project Center view, it works again. This is a fact. But if you apply ths view again without the filter and manually you create the same filter in the page of the Project Center view, it works and does not return the 'Unkown error', which breaks the hypothesis of duplicated entries in the database or field corruption.

    So in my humble opinion, I guess that the problem should be in the Project Central configuration code.

     

     


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner
    Friday, January 20, 2012 1:48 PM
  • Hi Ricardo,

    your conclusion on point 4 would be dependent on the same query being made when you:

    A) Make a filter through the Server Settings -> Modify Views

    B) Make a manual filter directly in Project Center

    It could be that the last uses a 'smarter' query that does not fail when there are duplicate records. Anyway we have seen clearly that our issue goes away once the duplicate records are removed and this is also confirmed by Microsoft. So if they can get a fix in place so that we no longer get any duplicate records this discussion then becomes acaemic.


    Regards, Stig
    Friday, January 20, 2012 2:35 PM
  • Hi Stig,

    The conclusion on point 4 is made using manual filter directly in Project Center (B).

    Yes, what matters is to find a solution for the issue.


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner
    Friday, January 20, 2012 4:48 PM
  • Stig hello again,

    Ive found a workaround that works for me. I made a little console application with VS2010 to attain that, and I explain here the most important steps of that application:

    - Create a new CF that also point to the same LookupTable.

    - Consult via SQL the list of projects and also the value for the problematic CF at the MSP_EpmProject_UserView.

    - Foreach project, with the problematic CF value, made a function to get the LookuptMemberUID of the value selected.

    - Insert to the new CF the LookupMember selected before.

     

    Now I have no problems at Project Center Views.

     

    Hope that helps


    Miguel Soler
    • Proposed as answer by Miguelet_ Thursday, January 26, 2012 6:45 PM
    Thursday, January 26, 2012 6:43 PM
  • Another easy solution:

     

    Make a CF with a formula, this formula will get the [Problematic_CF]. Republish all projects.

     

    Et voila.

    Hope that helps ;)


    Miguel Soler
    • Proposed as answer by Miguelet_ Thursday, January 26, 2012 6:45 PM
    Thursday, January 26, 2012 6:45 PM
  • See the possible solution presented by Brian Smith.

    It addresses a problem of duplicated information in project enterprise fields with lookup table, but it also addresses the 'unkown error' on Project Center views with embedded filter.

    The link is:

    http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/a4ae3e92-6327-44bf-8342-48205b7860de/


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Friday, April 20, 2012 4:40 AM
  • Many thanks for updating.

    But this is really only a workaround to temporarily resolve the issue and not the real solution. The solution will be there with the permanent fix that Microsoft has in their queue to avoid that duplicate values are saved.


    Regards, Stig

    Friday, April 20, 2012 8:19 AM
  • Stig,

    According to Brian Smith (MS), this is the solution. The CU of March 2012 fixes the problem and the scripts of the article of KB 2598251 delete the unecessary entries in the publish database of projects with this issue, because the CU does not revert it. Only prevent it.

    I applied in two of my customers with this problem in the last two days and it worked in both. Probably there should be other cases that this KB does not apply but you should try it!


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Friday, April 20, 2012 9:36 PM
  • You are right. I missed that there had been a new release of the CU on March 7. Sorry for that.

    Regards, Stig

    Saturday, April 21, 2012 6:41 PM
  • Hi everyone,

    According to Brian Smith (MS), the KB at http://support.microsoft.com/kb/2598251 , V 4.0 now has the scripts, and I have also detailed the relationship with the recent February 2012 CUs (Hint: copy the scripts from the KB).

    The hot fix that prevents the issue happens again is included in the CU of Abril 2012 and not in the February as written before.

    It is included in the CU of April, specifically in the hot fix KB 2598272. Read details in the KB in the link below

    http://support.microsoft.com/kb/2598272

    To get the Apr 12 CU links are:

    For Project Server 2010 + SharePoint family

    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2598152&kbln=en-us

    For Project 2010

    http://support.microsoft.com/kb/2598274


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    • Proposed as answer by R.Segawa Thursday, May 3, 2012 1:46 PM
    Thursday, May 3, 2012 1:46 PM