How to check a project in through the database

OK, so that was a mean trick.  Using a title like that to get you and your search engine to read about why you shouldn’t check a project in through the database.  In the words of Douglas Adams “It doesn’t necessarily get you where you wanted to go, but it turns out to be where you needed to be.”

With each successive version of Project Server we try to discourage direct database access more and more – and with 2007 we don’t even document the ones we want you to stay away from.  There may be times when you do need to get to these databases to read stuff if it isn’t available through the PSI or reporting database – and some very rare cases where bugs may lead to some database update being needed to resolve some data issue.  But in almost all cases a checked out project can be checked in without resorting to SQL.  The following steps are similar to how you would troubleshoot other queue problems – but are presented here specifically to work with checked out projects.

1. The best first step if a project that you have closed and checked in says it is still checked out is to open read-only, then close – and then leave a short while and try and re-open.  This should flush through any pending check-in that gets caused by the closing bug in Project Professional 2007.  As mentioned before – if it is a large project or your server is VERY busy the save can take a little while – so patience may also be required.

2. Assuming step 1 didn’t help then time to look at the queue.  First we need to confirm it is still working.  A couple of options here – first in Manage Queue add the Job Completion State of “Success” to the view.  This should then show you what has been working.  A successful job in the recent past – or a job that says “Processing” and the % complete is still increasing are a good indication that things are working (just not for you).  A second check if this doesn’t make things clear is to look at Task Manager on the Application Server (Right click the task bar and select Task Manager is a quick way to get this running) and check that you see multiple instances of Microsoft.Office.Project.Server.Queuing.exe on the Processes tab.  There should be (Number of Shared Services Providers with provisioned PWA site) + 1 instances.  So in most cases 2 – but possibly more.  Just seeing 1 is an indication that when the service started the database was inaccessible to the service so it could start the SSP specific instances.  Re-starting the service should resolve this.  just because in Administrative Tools, Services it says “Started” next to the service does not mean it is working!

3. So we know the queue is working and I am guessing at this point that you have a “Waiting to be processed” against a Project check-in job.  And you may have selected a “Force check-in” several times too.  The word “Force” here is a misnomer – and should really be worded “Please check-in when you are ready”.  If the job is waiting then it is waiting for something, and no amount of reboots, queue stop/starts will shift it.  We need to look deeper to find out what’s holding things up.  In Manage Queue set the Job History to go back far enough to see any activity for the problem project, set the Filter Type to By Project and then just select the project you are interested in.  This should then show your pending check-in job as well as what is blocking it.

image

4. So now you should see what is blocking the check-in, and the owner will show who was the last person doing something.  In my case it was a save from Project Professional that hadn’t complete (still “getting queued” – which means data is coming from the client cache to the message queue). 

5. If you are not the owner of the blocking job then get that person to repeat step 1.  This should allow the job to complete; although in the queue it may show as a cancel and re-save.  In this case the cancellation is done by the server as the save was in the very early stage.  If this worked then all is now well and you can get at the project.  This is the best resolution because NO DATA IS LOST!

6. If you can’t find the owner, or they have since deleted their local cache then step 5 will not be possible and the only option then is to cancel the job (after also checking the Advanced Option, Cancel jobs getting enqueued and optionally un-checking Cancel subsequent jobs in the correlation.)  Once the job is canceled then any subsequent jobs should complete OK and you are back in business.  Any changes made by the user who was saving WILL BE LOST!

And if this doesn’t work for you and you really do need to check-in through the database – let me know!

Technorati Tags: Project Server 2007