How to get the missing Project Server 2016 user views from PWA database
Installing SharePoint updates in Project Server 2016 environment sometimes lead to missing Project Server user views in content database.
By Default, the PWA database has user views for project, task and assignment. After installing the updates, the content database stopped showing these user views. In the below screen of content database, you can see there is no user view as “pjrep.MSP_EpmProject_UserView“, and other user views related to task and assignment are also missing.
To recover the content database, one can use PowerShell command, which will repair the PWA Instance. In most of the cases when we run the below command, the command status shows “Success” and the missing views start showing up in content database.
Repair-SPProjectWebInstance -RepairRule 7
But this was not the case while working in my environment. When I tried running the command, the status showed as ‘Failed’ and the content database didn’t get repaired as expected.
I then checked the ULS log to debug the issue. Here’s the screen of that log:
Looking at the log, I found that the problem existed in the store procedure – “MSP_Epm_GenerateAllMultiValueAssociationViews”.
In the below screen, you can see the highlighted line causing the problem:
I changed that line in the store procedure to fix the issue. Here’s what I replaced it with-
CREATE TABLE #TBL_Reporting_MutliValuedCF(
CF_INDEX int IDENTITY (1,1)
I then re-ran the same repair command. And as you can see, this time the status showed “Succeeded”
On checking the content database of PWA Project Server, I could see the missing views available now.
Hope it helps!