Here’s a common issue I am finally getting around to blogging up: certain projects always seem to throw an error on Publish, specifically on the part of the Publish job where the Publish database data is pushed into the Reporting database. Everything works fine in Project Server, but the Reporting database (RDB) doesn’t refresh.
This issue is almost always caused by somebody messing with key settings in the SharePoint site attached to the project. You’ll be able to determine if that is the case when you go into the Project Server queue and see something like this:
GeneralQueueJobFailed (26000) – ReportingWSSSync.WSSSyncMessageEx. Details: id=’26000′ name=’GeneralQueueJobFailed’ uid=’dae69759-a3c3-4ac4-a3bf-032022b0dd17′ JobUID=’41f0e5ef-9ca4-4edd-bcf6-f5fca87b3ae8′ ComputerName=’mypc’ GroupType=’ReportingWSSSync’ MessageType=’WSSSyncMessageEx’ MessageId=’1′ Stage=”. For more details, check the ULS logs on machine myserver for entries with JobUID 41f0e5ef-9ca4-4edd-bcf6-f5fca87b3ae8.
Check the ULS log, and it will tell you exactly what’s wrong with the SharePoint site in question.
PWA:http://mycompany.com/PWA, ServiceApp:Project Web App Service Application, User:myuser, PSI: [RDS] Failed to transfer SP list 1101 associated with project ‘920997d9-050c-4b54-84f2-7d365cbf3449’ to project server reporting database. Error: Microsoft.Office.Project.Reporting.ProjectReportingPublic.ReportException: The field: ‘Category’ on the list for project ‘920997d9-050c-4b54-84f2-7d365cbf3449’ should have type Choice. It was found having a different type (MultiChoice)…
…indicates that someone has changed the default Category field from a single select to a multiselect.
PWA:http://mycompany.com/PWA, ServiceApp:Project Web App Service Application, User:myuser, PSI: [RDS] Failed to transfer SP list 1101 associated with project ‘d32cf50b-8ccf-4405-8251-d26fb59c5d68’ to project server reporting database. Error: Microsoft.Office.Project.Reporting.ProjectReportingPublic.ReportException: Failed to prepare the transfer of SP list 1101 for project ‘d32cf50b-8ccf-4405-8251-d26fb59c5d68’. The field AssignedTo was missing from the SP list and was ignored.
…indicates that someone has deleted the AssignedTo field from the relevant list. You’ll also be able to determine which list specifically causing the issues, i.e. 1100 equals the Risk list and 1101 correlates to the Issues list.
As a little background to this discussion, Project Server has certain structures hard coded into the RDB. Specifically, every time a project is published, a routine hits the Website and pulls a dozen or so specifically named fields from the key Project Server lists and libraries on that site: Deliverables, Issues, Risks and Project Documents.
If a user inadvertently deletes any of those entities – or modifies them in such a way that the RDB can’t find them anymore, an error will be thrown. If the organization isn’t leveraging the RDB for custom reporting, that’s probably not an issue, as it really doesn’t matter what’s in the RDB. If the organization is leveraging custom reporting however, this becomes a priority.
Fixing Deleted Lists and Libraries
The first scenario we’ll look at is fixing deleted lists or libraries. This might happen if the site owner decides “Hey, I don’t need this Issues list.” and simply deletes it. To fix this, navigate to the site in question, turn off the Project Server feature, and then reenable it. That reprovisions lists that don’t already exist on the site – but doesn’t seem to impact lists already on the site.
Fixing Deleted or Modified Columns
The second scenario would involve the user modifying or deleting columns. In this case, simply add a new column with the expected name back to the list in question. Check a working project site to confirm the correct name for the field – specifically check the URL for the column in a working site to confirm that you’re creating a new column with the appropriate system identifier, and not the alias.
For example, “Assigned To” should be created as “AssignedTo.” Once the field has been created with the appropriate system name, go back and rename it to something a bit more user friendly, i.e. “Assigned To.” It will keep the original name, but display as the more user friendly “Assigned To.”
Fixing the Linked Column
The only column that can’t be fixed in this way is the Linked column. This column captures the links between list items and project tasks. That’s specifically provisioned as part of the list, and can’t simply be added back into the list using the method above. If there’s no data in the list, go ahead and delete the list and reprovision it using the technique above.
If the user has deleted this column and has data in the list, you’ll have to bring out the big guns. Here’s the approach that worked for me:
1) Provision a blank team site and add the Project Server Lists feature to this site. That site will be a parking lot for our data while we fix the list in our source site.
2) In the source site, navigate to Site Settings > Content and Structure.
3) Use the Content and Structure interface to copy the list data to our temporary site.
4) Delete the list in the source site.
5) Reprovision the Project Server list items in the source site (i.e. Site Settings > Site Features > Deactivate and Activate)
6) Use the Content and Structure to copy the data back from our temporary site to the source site.
7) Delete the temporary site to clean up.
Publish the project to refresh the RDB and check the queue to ensure everything processed as it should, and you should be good to go – until the next time an end user deletes a field. Note that you may have to do this a couple of times on the site, as the ULS logs seem to only catch the first error on a site before failing. If multiple changes have been made to the site, you may need to review the logs a couple of times to ensure you got everything.