A customer of mine recently had to recover from a worst case corruption scenario; one in which reverting to a backup was not an option. In short a hardware change resulted in progressive database corruption that was not picked up for over two weeks(!), leaving few options but to attempt to recover the corruption in-place.
Firstly though we had to fix the SQL databases, for that the following command was needed;
alter database ProjectServer_Published SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB(‘ProjectServer_Published’, REPAIR_ALLOW_DATA_LOSS)
alter database ProjectServer_Published SET MULTI_USER;
Yes that parameter does say “Allow Data Loss”, once you run this there is no going back, the databases are now free of consistency errors and other corruptions but in order to reach that state the DBCC command probably had to delete data!
So what now?
Well (fortunately for once!!) Project Server and Project Professional both are no stranger to application level corruption (*cough* 2007 *cough*), so despite DBCC repairing 7000+ consistency errors in the published database alone we are not without options.
So let’s look at some of the errors we had to deal with, firstly the MS Project client side errors and how we dealt with them.
MS Project Errors
Error 1. There is a circular relationship in task …
This one was surprising, but a sign that most likely the last save attempt for this particular schedule wrote corruption to some of the tasks.
To correct this you need to identify the task and correct or delete the predecessors, in my experience the circular reference is pretty obvious so fixing it is quick, however if that’s not possible then I’ve previously found this procedure helpful: http://www.domorethanmanage.com/articles/2008/05/29/Resolvingcircularreferenc.html
Error 2. Save Error’s 9000(0x2328) and 26005(0x6595)
I’ve put these two together as the solution is the same, in fact this procedure also applies to the Publish errors that you may get also see.
If you see the above, the first thing to try is an administrative restore, however if that fails there is one last resort;
The “XML Round Trip” method:
1. Open the project in MS Project.
2. File, Save As, Save As File.
3. When prompted select to save with “All Enterprise Custom Fields”.
4. In the file dialog select the Save as type: XML
5. Save the file to your local computer.
6. Once saved, close the project (don’t forget to check in!) and reopen the saved XML file, when prompted choose to import ‘As a new project’.
7. Once opened, select Save As and save the project to the project server using the exact same project name.
8. When prompted say yes to overwrite the existing project (you did check it in right?)
9. Publish and your done.
Note; This process will overwrite any previously saved data for that project, the impacts of this are as follows;
- In-progress timesheets will be affected, remember that internally all tasks and assignments are recreated, so existing OPEN timesheet periods will be updated. This may result in un-submitted timesheets losing actual work, or worse in progress timesheets failing to submit.
- Reports or custom add-in’s relying on GUID’s will likely have issues.
Error 3: Unexpected problem occurred while opening the file
If you see this, then hopefully one of the following works, if not you might be out of luck!
- Open the project from the local project cache.
- Open the PUBLISHED version of the project by selecting the appropriate Store from the open dialog.
- Restore the project from administrative backup.
Next, a number of errors in PWA can show up as a result of corruption, it’s worth mentioning that pretty much all of the data in PWA comes solely from the Published database. This is good to know because typically 99% of them can be fixed by simply re-publishing!
That’s all good when dealing with a single project, however when your dealing with multiple project corruption, or simply more commonly when a view fails and you don’t know *what* project caused it this method of bulk-publishing will come in handy:
Bulk Publishing Projects using ProjTool
Firstly get yourself ProjTool 2010: http://blogs.msdn.com/b/project_programmability/archive/2010/11/03/projtool-for-project-server-2010.aspx
This tool is extremely powerful (IE destructive!) and should be used with caution, but just completing these steps you don’t need to get too deep into the tool so your not risking too much:
1. Open ProjTool, enter project server URL and select Windows for Authentication.
2. Select the projects to republish (multi-select by holding shift).
3. Click Publish from the Toolbar, when prompted select YES for a FULL Publish.
From experience selecting over a couple of hundred projects will cause this to fail, but I have often used this to queue up 80+ projects for a republish.
Note however that almost ALWAYS ProjTool will report a problem in the queue, that’s because it only waits for a fixed time period for the queue jobs to complete and that never seems to be enough.
PWA View Errors
(The view failed to load. Press OK to reload…)
(An unknown error has occurred.)
These types of errors are likely caused by one of the following:
- Corruption in the project data being viewed
- Basic corruption in the view configuration.
For #1 this can be a custom field value that is filtered where one project has an invalid (null?) value for the field in question, so often just removing any filters configured on the view will fix this.
If you identify the filter in question then see the tips above about republishing (or possibly re-saving AND re-publishing) some or all projects to correct the data.
For #2 there are few options other than to recreate the view, copying the current view sometimes helps but not reliably.
Project Workspace Permissions Issues
I’m not going to cover how to restore document or general SharePoint data corruption in this blog, but in terms of Project Server usage you’ll most likely have issues relating to to the project permissions synchronisation, for this see the following link: http://nearbaseline.com.au/blog/2011/02/resetting-lost-permissions-in-project-server-2010/
Duplication of Custom Field values in PDPs:
This one turned out to be just an odd co-incidence in timing see the following recent Microsoft kb article if you see this issue;
Finally Reporting Database Refresh
Once all the above has been corrected you may still see some errors in Reporting jobs in the queue;
ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint
There are a few options available for this problem, two are well described here: http://www.epmpartners.com.au/blog/insert-statement-conflicted-with-the-foreign-key-constraint/
I’d suggest the RDB Refresh (Option 2 in the blog linked), this will take some time (hours typically), and any failures will result in projects NOT being listed in the reporting database at all. This is because the job removes and resynchronises the projects, so typically after running this job I usually use ProjTool to do a bulk publish (see above).
The moral of this story is simple: Make sure your SQL DBA regularly uses DBCC CHECKDB (http://www.sql-server-pro.com/dbcc-checkdb.html) to ensure you don’t get into this sort of mess!
This is an issue I have seen now with two different Project Server 2010 clients;
- Certain Projects cannot be opened in either PWA or MS Project.
- Oddly the same projects do not appear to exist in the Draft database, but DO appear in the Published database.
- Projects cannot be deleted from Server Settings after selecting the Published version and hitting delete.
Delete Queue job fails and review of the ULS log shows the following:
10/14/2010 11:30:40.33 Microsoft.Office.Project.Server (0x14A8) 0x172C Project Server Server-side Project Operations 7gvo Critical Standard Information:PSI Entry Point: Project User: i:0#.w|domainadministrator Correlation Id: e63eca62-f9fb-40d6-af52-db59bff5b7cd PWA Site URL: http://project2010:82/PWA SSP Name: Project Server Service PSError: ProjectDeleteFailure (23006) The request to delete a project encountered a problem – the relevant job failed in the Queue. Project UID: 01c7c325-1704-4269-8316-ab1b0bc85d07. Sub-job type where failure occurred: Microsoft.Office.Project.Server.BusinessLayer.QueueMsg.
AdjustTimeSheetForDeletedProjectMessage. Sub-job ID where failure occurred: . Specific stage in the sub-job where failure occurred: . Does this failure block the correlated job group: Undefined. See the ‘Manage Queue’ page in PWA for more details.
Additionally enabling Verbose ULS logging for all Project Server events turns up this one:
10/14/2010 11:50:05.33 Microsoft.Office.Project.Server (0x14A8) 0x1434 Project Server Timesheet myzd Verbose PWA:http://project2010:82/PWA, ServiceApp:Project Server Service, User:i:0#.w|domainadministrator, PSI: Start ApproveProjectTimesheetLines(approvedTimesheetLines=’ ‘, rejectedTimesheetLines=… [GUID’s removed] … 10/14/2010 11:50:05.33 Microsoft.Office.Project.Server (0x14A8) 0x1434 Project Server Timesheet myzl Verbose Error is: GeneralItemDoesNotExist. Details: . Standard Information: PSI Entry Point: Project User: i:0#.w|domainadministrator Correlation Id: e63eca62-f9fb-40d6-af52-db59bff5b7cd PWA Site URL: http://project2010:82/PWA SSP Name: Project Server Service PSError: GeneralItemDoesNotExist (10000)
It took the enabling of Verbose logging to figure out the cause, the give away was the timesheet line delete job, it seems that for some reason the TS Line GUID’s above were causing the failure.
So the solution was relatively simple, identify the Timesheets (and respective lines) and delete them manually.
To that end I ended up creating a couple of SQL scripts;
Identify all projects which exist in the Published Database but not in Draft.
— Query to identify projects in Publish but not in Draft
SELECT PubProj.PROJ_UID, PubProj.PROJ_NAME AS Published, DrafProj.PROJ_NAME AS Draft
FROM ProjectServer_Published.dbo.MSP_PROJECTS AS PubProj
LEFT OUTER JOIN ProjectServer_Draft.dbo.MSP_PROJECTS AS DrafProj ON PubProj.PROJ_UID = DrafProj.PROJ_UID
WHERE DrafProj.PROJ_NAME IS NULL
Identify all Timesheets with lines against projects not in the Draft database.
— Query to identify timesheets with lines against projects not in draft
SELECT MSP_TIMESHEETS.TS_UID, MSP_TIMESHEETS.CREATED_DATE, MSP_TIMESHEETS.TS_CACHED_RES_NAME, MSP_TIMESHEETS.TS_COMMENTS
,RepTSP.PeriodName, RepTSP.StartDate, RepTSP.EndDate
INNER JOIN MSP_TIMESHEET_LINES ON MSP_TIMESHEETS.TS_UID = MSP_TIMESHEET_LINES.TS_UID
INNER JOIN ProjectServer_Reporting.dbo.MSP_TimesheetPeriod AS RepTSP ON MSP_TIMESHEETS.WPRD_UID = RepTSP.PeriodUID
MSP_TIMESHEET_LINES.PROJ_UID IN (SELECT PubProj.PROJ_UID
FROM ProjectServer_Published.dbo.MSP_PROJECTS AS PubProj
LEFT OUTER JOIN ProjectServer_Draft.dbo.MSP_PROJECTS AS DrafProj ON PubProj.PROJ_UID = DrafProj.PROJ_UID
WHERE DrafProj.PROJ_NAME IS NULL)
ORDER BY CREATED_DATE
Note: For both script’s replace your ProjectServer_* name, and then run this script against your PUBLISHED database.
Running the 1st script is not mandatory, although it can be useful as in my case it identified a few more projects with this issue. However: Ignore the ‘eGlobal …” project(s) and do not attempt by any means to remove them, you have been warned!
The second script will identify line by line timesheets that need to be edited / deleted before you can delete from Published the problematic project. This needs to be done in the usual ways, probably by the Resource themselves unless using delegation or something similar.
Good question! I was not able to identify the root cause of this one, although I did notice it occurring to me during a training session that I was hosting, in fact the issue occurred on the test project I was working on while testing the creation and use of Timesheet approval rules and of course submitting / approving / rejecting test timesheets. However despite that clue I was never able to reliably reproduce the issue, please leave a comment if you have any other ideas!
Hope that helps someone out there!
This is a useful solution; with it you can update on a PDP an Excel spreadsheet saved in project’s workspace document library and then potentially update custom fields from the results.
However it is far from a complete solution to download and deploy.
First issue – Admin rights required to use Web-Part:
Installed as per instructions (build / package / run script), then when adding the web part this familiar error occurred:
“An unexpected error has occurred.”
A quick look at the ULS shows more details:
The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security.
Now that’s interesting as I had this exact same issue a few months back working with a 2007 PSI project, it turns out the answer is rather simple; the web service is attempting to write to the event log using “EventLog.WriteEntry” method calls, however in Windows 2008 and above this is restricted to Administrators only. So as the PWA users are not local admins the error was guaranteed.
A quick solution is to comment out each line in the code beginning with “EventLog.” of course that will disable the logging, so you would be better off doing that after you get everything else working. The file in the solution that you are after is CostAndBenefiXlsWebPart.cs in the WebPart.CostBenefit project and you’ll find about half a dozen envent log entries to comment out.
Second issue – The default site template location:
While your in that file commenting out lines, you’ll most likely notice this;
private string pdpSitePath = @”http://project.contoso.com/PWA/MajorPWSTemplateSite;”
Although the solution will work without changing this, as that is only used when no workspace site exists (like when in server settings – project detail pages), updating that to point to your http://server/pwa/template workspace site template will fix any file not found issues when setting up your PDPs.
Third issue – Location of the Excel document used:
If you’ve gotten this far then like me you might be seeing something like this:
No item exists at http://servername/_layouts/xlviewer.aspx?list=b73da3b8-b48e-400c-8fe0-9b6a48442518&id=1&DefaultItemOpen=1&Edit=1. It may have been deleted or renamed by another user.
A little digging turned up the cause of this one to be the embedded ID in that URL “&id=1”, which means that it is trying to open the document with ID 1 in that particular list. This again is hard coded in the same .cs file as above (line 191), so you can either make certain that in your template above the first file saved to the Project Documents folder is your xls sheet, or modify the code again.
So now we should have a working webpart referencing our Excel sheet saved in the project workspace site, there are some obvious limitations, not least of all the inability to use any other spreadsheet in the webpart. But I guess that’s why they call it a “solution starter”.
Update 23/10/2010: Most of the points above have been fixed in the more recent updates to the solution starter package, make sure you update your code from Codeplex: http://code.msdn.microsoft.com/P2010SolutionStarter
Been very quiet on this blog the last couple of weeks, largely due to the fact that I’ve just kicked off my second PS2010 project (this one for one of the big mobile network management companies), while at the same time being in the thick of the first round of system testing for my first 2010 project!
I’ve got a handful of things I wanted to blog about but let’s start with some issues that I have come across, some of these I plan to log with MS, however none were really bad enough to cause any major concern.
1. Workspace Template Risks List ‘Exposure’ Unexpected Error
When you create a blank template site (not linked to a project) an ‘Unexpected Error’ will occur when you try to edit the field behind the default ‘Exposure’ field. This doesn’t happen when the site is linked to a project through the normal project creation.
Fortunately the standard calculation (Consequence * Likelihood) is all that I usually use anyway so this is a non-issue.
2. No Spaces Between Tasks when using PWA online editing
This one is causing some frustration, basically the Project Web App online editing does not support blank lines between tasks at all. Rather annoying as just about everyone I know uses spaces to make large projects more readable.
3. BCM Project Professional 2007 Crash when spaces exist between tasks
This one is related to #2 above, if you save a project template containing spaces between tasks (blank lines) and associate it with an Enterprise Project Type, then it seems when in BCM mode attempting to open a project created by that type will cause problems for Project 2007 client, but not 2010.
I didn’t fully test this one out to find if there are any other factors, but after removing the blank lines from my templates both this and #2 were fixed.
5. Project Client version restriction ignores CU build numbers
This one is more of an annoyance, it seems that the new feature in server settings to restrict the version number of connecting Project Pro clients only recognises major build numbers, ie Service Packs. Meaning that trying to force all 2007 clients in BCM mode to run at least Feb2010 CU will be just as troublesome as it always was.
5. Project Template used by EPT with Budget Resources assigned causes failure to create project
This is probably the worst issue I have found, I like the Budget Cost feature but it doesn’t seem to be the most widely used feature if an issue like this can slip through testing. I’m probably going to log this one with MS, but for this particular customer we simply modified our procedures to work around it. Here is my full issue description:
Attempting to save an Enterprise Template and associate it with an Enterprise Project Type when the template includes Budget Resources assigned to the Project Summary Task causes a failure in the project creation queue job(s).
Project Server 2010 RTM
Project Professional 2007 or 2010
- Create a new blank project in Project Professional
- From Tools – Options menu select ‘Show Project Summary Task’ (or equiv ribbon in 2010)
- Select Resource Sheet view, then add a single Cost Resource named ‘Test’
- Double click the resource to edit properties, and select ‘Budget’ to set the resource as a Budget Cost resource
- Assign the Test resource to the Project Summary Task
- Save As to save as an Enterprise Template named ‘Test Template’
- Open PWA, go to Server Settings – Enterprise Project Types
- Create a new Project Type as follows;
- Name: Test type
- Workflow: No Workflow
- New Project Page: Proposal Details (or anything else)
- Project Plan Template: Test Template
- All other values default
- Select Project Centre, New Project – “Test type”
- Enter the details and Save
Queue job fails with the following error:
Row: ASSN_UID=’18a3ec42-a1ad-415b-9e38-5b822f38750e’ PROJ_UID=’09c32ca4-5d86-4b0b-91f0-21e8abe97e3c’
Error TaskNotFound (7021) – column
GeneralQueueJobFailed (26000) – ProjectCreate.ProjectAddToMessage. Details: id=’26000′ name=’GeneralQueueJobFailed’ uid=’b6640d9d-36ab-496d-b210-7b765ea852ea’ JobUID=’abf46cad-10b1-4e7c-94b8-6beeb76b30c5′ ComputerName=’AU-GLB-MSP04′ GroupType=’ProjectCreate’ MessageType=’ProjectAddToMessage’ MessageId=’1′ Stage=”. For more details, check the ULS logs on machine AU-GLB-MSP04 for entries with JobUID abf46cad-10b1-4e7c-94b8-6beeb76b30c5.