Project Server 2016: Missing some UserViews?


We are still hunting down the cause of this issue but we are seeing either fresh Project Server 2016 Project Web App instances or updates to Project Server 2016 causing some of the usual views in the pjrep schema used for reporting to be dropped. One example of a missing view is the pjrep.MSP_EpmProject_UserView.  If you are familiar with 2013 then these views now in the pjrep schema were in the dbo schema of the Project Server 2013 database.

snip_20160708073813

 

You can re-create the views by running the following PowerShell command in the SharePoint Management Shell

Repair-SPProjectWebInstance <PWA URL> -RepairRule 7

snip_20160708073945

Once this complete, which only take s a few seconds, you should see the familiar views:

snip_20160708074218

I plan to do a more complete description of the ‘Repair’ command soon as I am sure you are all thinking  “What are the other RepairRules?”.

And a reminder – we only support direct access via these views if there is only a single PWA site in the content database.  So if you need direct access such as SQL Server Reporting Services then you will need to add additional content databases and have each PWA in its own.  For OLAP and OData reporting this restriction does not apply. *** And thanks Steve Sanderlin for the reminder – we don’t just not support direct access when there is more than 1 PWA – but we don’t create the views ***


Comments (24)

  1. Thanks, Brian.
    What are the other Repair Rules? 🙂
    Regards
    Christoph

  2. This is great news!! I have two separate local installs where these tables did not appear and I was trying to re-create them myself. Thanks!!

  3. I think there are mistake in stored procedure [pjrep].[MSP_Epm_GenerateAllMultiValueAssociationViews]
    in rows: SET @i = 1
    and
    WHILE (@i 1
    BEGIN
    PRINT ‘Skipping Reporting View creation. Reporting views are created for single tenant databases only’
    RETURN @Result
    END
    CREATE TABLE #TBL_Reporting_MutliValuedCF(
    CF_INDEX int IDENTITY (0,1),–Here is temp table identity, starts from 0, not 1
    CustomFieldTypeGuid uniqueidentifier)
    CREATE CLUSTERED INDEX CL ON #TBL_Reporting_MutliValuedCF(CustomFieldTypeGuid)
    SET @i = 0–original text was SET @i = 1
    INSERT INTO #TBL_Reporting_MutliValuedCF(CustomFieldTypeGuid)
    SELECT AttributeTypeUID AS CustomFieldTypeGuid FROM pjrep.MSP_TVF_EpmMetadataAttribute(@siteId)
    WHERE AttributeIsIntrinsic = 0 AND AttributeIsMultiValueEnabled = 1
    SELECT @Count = COUNT(CustomFieldTypeGuid) FROM #TBL_Reporting_MutliValuedCF
    WHILE (@i < @Count)–original text was WHILE (@i <= @Count)
    BEGIN
    SELECT
    @CustomFieldTypeGuid = CustomFieldTypeGuid
    FROM #TBL_Reporting_MutliValuedCF
    WHERE CF_INDEX = @i
    –in original procedure @CustomFieldTypeGuid always was NULL
    EXEC @Result = pjrep.MSP_Epm_GenerateMultiValueAssociationView @siteId, @CustomFieldTypeGuid
    IF @Result != 0 GOTO LblError
    SELECT @i = @i + 1
    END
    LblError:
    DROP TABLE #TBL_Reporting_MutliValuedCF
    RETURN @Result
    END

    1. Thanks Sergey, taking a look at this now.
      Best regards,
      Brian.

      1. Christoph Mülder says:

        Hi Brian,
        is this a known issue now, or do we need to open a call for it?
        Thanks
        Christoph

        1. Opening an incident would probably help Christoph – it is ‘known’ but will get more attention with a customer behind it.
          Best regards,
          Brian.

    2. CarlD says:

      confirmed the bug from my side! If you have MV fields then this sproc is executed in the repair, and this fails unless you change as noted to

      CREATE TABLE #TBL_Reporting_MutliValuedCF(
      CF_INDEX int IDENTITY (1,1),

      1. Christoph Mülder says:

        Hi
        yes. it fails if there is exactly 1 multi value custom field. If there are none, it will not execute at all. If there are more than one, it will just create the views for all multi value fields except the first it gets.

  4. Kurt Verhaegen says:

    Hi Brian,
    Could it be that OLAP cube configuration is not working if the user_views don’t exist.
    I can configure the OLAP cube (add ent. custom fields to cube configuration), however the configuration is not saved.
    OLAP cube is building with standard dimensions & measures.
    I’m having 2 PWA sites provisioned (instances).
    Or is this independent of the missing userViews?
    Regards,
    Kurt

  5. Trutz-Sebastian Stephani says:

    Is it supported to move PWA instances between content databases using the Move-SPSite/Copy-SPSite PowerShell command now that all “content” is stored in the SharePoint content DB?

    1. I’ve successfully used Move-SPSite – and that is ideal to move a site to its own Content DB for reporting purposes. I’ve not used Copy-SPSite – I’d be a bit more hesitant saying that is supported.
      Best regards,
      Brian.

      1. Trutz-Sebastian Stephani says:

        Thank you for this confirmation.

  6. Ray Letts says:

    Hi all and thank you Brian for this. It did help certainly however it brought back the default out of the box views without any custom fields added.
    We came up with a trick we can offer to help others to get Project Server to redo the views with your organization’s custom fields.
    Using the service that drops and recreates the views when custom fields are modified/created we renamed all the custom fields; that rebuilt the views. then we renamed them back to the original names and we now had our views with the organization’s custom fields.
    cheers
    Ray Letts
    Arbutus Solutions

    1. Thanks for sharing this Ray – we do have a hotfix in progress for this so hopefully we will have a better solution soon.
      Best regards,
      Brian.

      1. Saeed Zaman says:

        May I request to let us know the scheduled CU release for the fix of Missing user views ?

        1. I’m following up on it Saeed, Mustaq – more news when I have it.
          Best regards,
          Brian

          1. We have the fix going through code review now Saeed/Mustaq and expect it to get into the March PU.
            Best regards,
            Brian.

          2. Mustaq says:

            Thanks for the update Brian Smith. We look forward for the same :).

          3. mustaq says:

            Issue is still occurring with March 2017 CU.

          4. Can you explain a bit more Mustaq? Are you finding the views get removed? Remember that if you have more than 1 PWA ina content DB then you don’t get views.
            Best regards,
            Brian.

          5. Hi Brian,
            Yes, we noticed views getting dropped even after March 2017 CU. I also confirm that we have only one PWA per content database.

  7. Mustaq Hussain says:

    Brian Smith, We are facing this issue of missing views very frequently, Can you please help us with scenarios that creates this issue and also if there is a ETA for the permanent fix. We are running the repair command to get this fixed. But we cannot do this frequently.

  8. flori says:

    For the two issues

    [pjrep].[MSP_Epm_GenerateAllUserViews]
    IF @SiteCount > 0 => IF @SiteCount > 1

    [pjrep].[MSP_Epm_GenerateAllMultiValueAssociationViews]
    CF_INDEX int IDENTITY (0,1), => CF_INDEX int IDENTITY (1,1),

    this might help.

    https://github.com/FlorianGrimm/Microsoft-Project-Server-2016-Fix-for-UserViews-and-MultiValue-Fields

Skip to main content