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


Comments (35)

  1. Ashish Gedam says:

    I still facing the same problem its not going to check in project. plz suggest i stuck up with my work

  2. Bryant Likes says:

    Thanks for the post on using the database πŸ˜‰

    It is nice to have those steps documented since we’ve run into this a few times at the client I’m working for.

    I don’t really need to checkin via the database, but I have found a few other things that tempt me to use the database:

    1) Trying to change end dates in the plan. Right now we set the constraints which works for most tasks, but not for milestones. If you change the constraint on the milestone (move it out one day for example) then PS sets the percent complete to 100%. The support person on our account sugguest we submit it twice but so far this hasn’t seemed to work (we have a custom WF that submits all the jobs and submitting two update jobs doesn’t solve the issue. Still looking into other options).

    2) Adding task notes to a task (not in the PSI).

    3) Anything else where the experience is not consistent between using the Project client and the PSI.

    I’m sure some of these things are just a lack of knowledge on our part, but it would be nice to know how to do some of the things the client does using the PSI instead. Like moving milestones.

    So it would be great to see a post (or posts) on this topic. πŸ™‚

    Thanks!

  3. BriSmith says:

    Hi Ashish,

    If you are following these steps where is it not working for you?

    Brian.

  4. BriSmith says:

    Hi Bryant,

    Certainly (2) will be a pain point – and depending on the scenario there will be things that might fit in (3).  Not sure about (1) – I’ll need to take a look.  Thanks for the posting – and I like your blog.  Good to see what Tim Sneath is up to as well.

    Best regards,

    Brian.

  5. Bryant Likes says:

    One more for you. πŸ™‚

    So since you can’t actually move a task’s start and end dates, we’ve been using constraints to schedule it. However, as I mentioned above, for milestone tasks this doesn’t work very well. So today I tried setting the actual start date to schedule the task.

    Doing this via the PSI seems to work perfectly except for the project client doesn’t pick it up. I’ve tried setting this value in both the PSI and the client and then comparing the database records and they are exactly the same (except the task_binary_data). But the project client does not pick up the fact that I’ve set the actual start date. Very strange.

    Any ideas or suggestions appreciated. πŸ™‚

    Thanks!

  6. BriSmith says:

    Thanks Bryant.  I’ll try and take a look at this next week.  Have you tried some of these scenarios through ProjTool whic is in the SDK?  I find that can be useful for seeing which fields are updatable in the recordsets.

    Best regards,

    Brian.

  7. Scott says:

    Brian,

    Great information and this would have saved me a lot of time had I found it a few weeks ago.  I discovered most of this after hours of trial and error – and patience.  But, I’ve found yet another problem with “Checkin Pending..”.  I’ve got a project that EVERYTIME I open it, I’m told there is a “Checkin pending”.  So, each time I am forced to open it read only and then close it before I can retrieve it from the server as a writeable project file.  Has anyone seen this behavior? I have no idea if it’s related, but this started happening after asking the team members to update their task status via PWA. I successfully approved, submitted, and published their updates, but then this weird “checkin pending” started happening.  Again, I have no idea if it’s related or not.  Thanks for a great blog.

  8. BriSmith says:

    Hi Scott,

    I am aware of some "unexplained" checking pending problems, and thanks for the details of when you think this started happening.  I’ll see if I can find more on this – but if you can come up with some good repeatable repro steps I would be happy to dig deeper.

    Best regards,

    Brian.

  9. Christoph Muelder says:

    Hi Brian,

    I understand that you want "hands off" the database and normally the way you described worked for me too.

    But these days I had a customer’s system with four projects having jobs "in progress" on between 0 and 50% hanging for up to three weeks in the queue.

    No options for canceling worked, no jobs before them blocking anything, no error messages for the jobs.

    Now I had to dig deep in the database and managed to kill the jobs there. The projects work again. I am aware that the projects may be damaged now but now I can at least restore them from the archive db.

    But for "normal" problems I agree with you and thanks for the explanations.

    Best regards

    Christoph

  10. BriSmith says:

    Thanks for your posting Christoph – and I agree – there are always exceptions.  Glad you got things working again.

    Brian.

  11. Charles Baldwin says:

    I just purchased a new HP Pavillion a6230n desktop computer, Prod # GN561AA, SN: CNH74406MH

    I am trying to export Outlook express messages from my old computer and import into Windows Mail on my new computer.  None of the self help files at Microsoft or HP have been successful in doing this.  I called Microsoft Technical support and was told this was a preinstalled program on an HP so for them to support their software would require a fee based service.  I called HP and was told for them to support this activity would be a fee based service.  I just bought a new HP computer with Microsoft operating systems and neither will support their products.  I strongly suggest that anyone looking to purchase new computers and soft ware look closely at other computers and operating systems.

  12. BriSmith says:

    Hi Charles,

    I’m sorry you have had a bad experience.  I know this isn’t Project related and hopefully if you have posted a similar question to other more relevant blogs or newsgroups you may get some help.  More details of any errors you are getting would also be useful.  Didn’t the file and settings transfer wizard help?

    It is the normal practice that the manufacturers are the first line of support for the software installed -and I would not see this being any different if you had other operating systems from the same manufacturer.  Let me know how you get on.

    Best regards,

    Brian.

  13. Ben Howard says:

    Hi Brian,

    Just tried this, glad to say it worked for a projet that was stuck. Quick question though, is there any difference between the terms enqueued and queued?

  14. BriSmith says:

    Hi Ben, No difference – just a new made up word.  Like statusing… :).

  15. David326 says:

    Brian, great tips on how to get the Process Queue rekicked.  My problem is simialr to those outlined in ‘HOW TO GET A PROJECT CHECKED IN THROUGH THE DATABASE’.

    Our Project Server installation is on a VM Server Farm with SQL and Project installed on seperate VMs.

    AP-PRJSRV-DEV and DB-PRJSRV-DEV.

    We only have 1 occurance of the Microsoft.Office.Project.Server.Queuing.exe running in task manager on the App/Web server.  Based on the previous article I would expect to have 2 occurances running.

    No matter how many times I stop and start the MSO Project Server Queue Service in services I cannot get more than one occurence to show in task manager.  The Event Log on the App/Web Server indicates the follow error. "Unable to start service process. SSP: 72d129d3-851f-4b4f-b1ec-1eeb9c33f45a Service: pjevtsvc" and "Unable to start service process. SSP: 72d129d3-851f-4b4f-b1ec-1eeb9c33f45a Service: ProjectQueueService"

    I have Started and Stopped the Event Service and Processing Queue Services many times.  It indicates started in Services but obviousely this in not the case.  My Queue has never processed data since our intial install on Feb 4th.  We installed SP1 that same day as well.   When I FILTER the Queue with Success and set my History Date before the install the grid display is empty.

    Any suggestions or ideas on how to get teh Project Queue Service and Event Service running?

  16. BriSmith says:

    Hi David,

    There may be better error messages in the ULS logs – but this can be caused by a DB access problem – so worth checking SQL to ensure you don’t have any "failure audits" for the account running the SSP.  The service itself is working OK – in this case started just means the initial instance is up and running.  Once this starts it checks to see which SSPs have PWA instances provisioned and will start extra instances.  It appears it is this step that is failing for you.  Hopefully the ULS and SQL logs will give the clues as to why.  It will be the account set as the administrative account for the SSP that will run the SSP instance services – whereas the initial instances run under the farm administrator’s account.

    Best regards,

    Brian.

  17. Lee Ann says:

    This was very enlightening and I believe now that I may need to look at the database.  I did look at the table for projects and the checked out and checkedout by fields are both NULL for the project.  

    My SharePoint, database and Project Server are all installed on one server.  We had network troubles last week that really messed with the timer service in SharePoint and now after reading this probably the Project Queue service, although I did not have specific event log enteries for the Queue service, but there were errors about not enough space to run ApplicationServerAdministrationServiceJob.  

    Anyway, one of our project managers had their project checked out during our network troubles and we have tried all the check-in methods we can find, but it still opens up as read-only and does not show up anywhere as needing to be checked in.  Unforturnately, I was out of the office when this all happened so not sure exactly what took place with the project, clearing cache, deleting files, etc.  When I look at the queue, it is running correctly now, but there are no jobs from 2/4 – 2/8. Sorry I don’t have anything insightful to add to the blog.  Just looking for some direction on how we might recover this project.  I guess there is always delete and recreate, but hoping not to have to go that route.  

  18. BriSmith says:

    Hi Lee Ann,

    Does it show "checked out" for everyone?  Usually if it looks checked in as far as the database and the "force check-in" page then it is only the user who last checked it in that sees this problem.  Cleaning the project from their cache may be the resolution here.

    Brian.

  19. Miguel Morales says:

    Hello, I have this same issue, the only difference is that I cannot resolve it via the Server Settings, Manage queue, check in projects, or cancelling the process. Can you please walk me through the Database force check in. In 2003 it was pretty easy in that the db table column "CheckedOut was in ten MSP_projects table, I do not see this field in 2007.

    Thanks in advance for your help.

    miguel@morales4.com

    602-320-4047

  20. mmorlaes says:

    Hello Ben, I’m still unable to force checkin a project can you please send me the instructions for performing this via the database?

    Thanks

    Miguel

  21. BriSmith says:

    Sorry Miguel – I am not going to give details on making database changes.  This can lose data and will render you system unsupportable.  Just because it was easy didn;t make it right :).  I would suggest opening a support incident with Microsoft to help get this resolved.

    Best regards,

    Brian.

  22. Aziz zekhnine says:

    Hi My name is Aziz and I am having the same problem

    I tried all the steps but none worked

    Thanks for your help

  23. Zekhnine says:

    Hi,

    This is Aziz again

    Problem is fixed now

  24. Lee Ann says:

    Hi Brian,

    We implemented on Project Server 2007 SP1 and I’ve read on other posts of yours that this check-in issue was know and should have been fixed in SP1.  Does anyone know if that happened.  I guess I should go check on MS site.  I really thought we got through our issue with project check-in, but it has reappeared.  I had a timer service permission issue that wasn’t letting anything through, but we ended up deleting the user’s profile, as no amount of reopening, closing, checking in, forcing the issue helped.  I am now concerned that we do not have 2 instances of queuing.exe since re-reading this post and I have stopped and started the service.  We have an instance of eventing.exe

  25. BriSmith says:

    Hi Lee Ann,

    It doesn’t sound like your queue service is running.  To see a good log entry O would suggest restarting the services through the Services applet – and then you should get an entry in the ULS logs detailing why it couldn’t start.

    The check-in issue "should" be fixed but I do hear a few cases coming in where customers are still having issues with this…

    Best regards,

    Brian.

  26. Andrea says:

    Hi there,

    We are continuing to have issues with check-in, particularly where the project remains in a permanent ‘pending’ state. Generally you can resolve it by the same person re-opening in a read only mode and then closing again but not always.

  27. Ashish.gedam@gmail.com says:

    I faced the same problem, last option i used to check in project,

    Steps to Check in project :

    1. Connect the SQL Server

    2. Click on Draft Data Base

    3. Open MSP_Project

    4. Identified the Project, its not going to cheak in

    5. Go to Cheack out Coulmn and chage spacified value to NULL

    6. Go to server setting and Check in Project

    7. you find project get cheack out

    Hope it will for you

  28. Brent says:

    I have the same problem here – we just stood this up last week.  Unfortunately, I put in the resources directly instead of using active directory.  Now, I am trying to put all of the resources in using active directory since I already have groups by office symbol set-up there.  Now I have to remove my original accounts that I set-up so I can put all the accounts (and resources) in directly by synchronizing active directory (except the admin accounts).  I have had a lot of problems removing the original accounts because they had projects.  I have archived the original projects to my local hard drive since there we not vey developed at this stage, but there are a couple of project files that I cannot get out of the system no matter what.  They show check-out to me, but are read only.  I tried to force the check-in and observed the jobs through the managing queue, but no luck.  I rebooted the server, with no change.  I would rather not mess with the SQL directly, but it would be better than reinstalling the whole thing  – I don’t have any critical data at this point.  Everything is on the same server.

    Any help would be greatly appreciated – Brent.Petersen@usafa.af.mil

  29. Elliott says:

    The steps you aid out mirror my problem exactly.  But I have a project plan that simply will not complete!  So, I suppose I really need to check-in through the database.  Please explain how this is done so I can convey the info to my DBA?

  30. BriSmith says:

    No Elliott – you need to find out why it will not complete.  Getting round the issue via the DB will just mask a potentially bigger problem.  A support incident may be needed to get to the root of the issue.

    Brian.

  31. BriSmith says:

    Hi Brent,

    I’d also suggest a support incident may be the best way to get to the bottom of your checked-out issue.

    Best regards,

    Brian

  32. mobrien118 says:

    I am having a problem where the checkin appears to have completed successfully on the server, but the client isn’t working. The only way to clear it up is to completely remove the cache folder from the user’s folder.

    It looks like it started after Microsoft had us install an IIS patch (to clear up some errors we were having on the server).

    Anyone else having this type of situation?

    –mobrien118

  33. BriSmith says:

    Hi mobrien118,

    I haven’t heard of this one. Do you have the details of the IIS patch?  Also what patch level are you at for Project and Project Server?

    Best regards,

    Brian.

  34. Dolly Pandey says:

    I am facing the same problem its not going to check in project. Everytime it just add a new job to the queue but the status remains as waiting to be processed.

    How do I check the project in through Database? Is there any SQL Script to do so?

  35. BriSmith says:

    Hi Dolly, as I mention in the blog there are reasons why the check in is not working and you just need to find out what they are and fix them and all will be good.

    1.  Is you queue processing other jobs?  If not then you need to troupleshoot this – just trying to restart would be the first step – then check the ULS logs.

    2.  If it is then it probably means there is some other job that the check-in is waiting for.  This may be a job from days or weeks ago so you need to display the history of queue jobs to see it.  Fix it – cancel if necessary and all the other jobs should then work.  

    3.  If the check-in is actually processing and failing then you probably need to open a support incident as some part of the check-in process, which includes applying status updates if there are any, may be failing.

    Hope this helps,

    Brian.