Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

How to check a project in through the database

How to check a project in through the database

Rate This
  • Comments 35

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

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
  • I still facing the same problem its not going to check in project. plz suggest i stuck up with my work

  • 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!

  • Hi Ashish,

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

    Brian.

  • 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.

  • 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!

  • 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.

  • 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.

  • 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.

  • 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

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

    Brian.

  • 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.

  • 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.

  • 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?

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

  • 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?

Page 1 of 3 (35 items) 123