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

  • Brian Smith's Microsoft Project Support Blog

    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.


    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

  • Brian Smith's Microsoft Project Support Blog

    Friends don't let friends delete their cache or cancel queue jobs


    Inspired by Brian Kennemer's e-mail tag line of "Friends don't let friends assign resources to summary tasks" I thought I would get back on my soapbox about the cache and queue.  I do appreciate that there are some early bugs around custom field display that require the occasional local cache deletion - and there are a couple of rare scenarios that will leave the queue in a bad way and things need canceling.  But generally many of the situations our customers run into can be resolved without recourse to either of these actions - which can both lead to DATA LOSS!

    A couple of examples from the queue:-

    Project Save from Project Professional - Getting Queued

    This means the data is flowing from the client cache to the server queue - and once it is all in the queue it will then be loaded into the Project Server database tables.  If the client goes away while this is happening (and this can be our fault as we don't handle Project closing very well) or the network goes down, or you hibernate your laptop as you race out of Starbucks, then the queue will just sit in this state.  If you cancel the queue job then the good data in the client cache will never see the light of day.  The correct approach is to identify from the queue where the save is coming from (the owner will display from the queue) and then get that person to re-connect their client and the getting queued should continue.  In some cases you will see the original save show as canceled but if you look in the ULS logs it will have a message along the lines of:-

    PWA:http://server1/PWA, SSP:SharedServices1, User:DOMAIN\username, PSI: WinProj.PreSaveProject [T:abf8f56f-e3d1-4139-9355-55ef33aa1378][U:079d778a-2a14-455a-a52e-3141b57e75ea][S:6521e25f-5c1c-41d3-a224-7a868e161c42][D:CLIENT1\ProjConf 2][J:abf8f56f-e3d1-4139-9355-55ef33aa1378][PS_AC][3] Cancelling correlation 2439f848-3966-44b7-a645-1ff7b6914f10 as it has 1 send incomplete winproj save jobs.

    which indicates the original save hadn't got very far so it cancels it from the server and starts again.  This was in fact the project that should have demonstrated this recovery at the project conference - but I didn't leave Project Professional connected to that profile for long enough (my fault - trying to present 3 hours of stuff in 75 minutes).  Another interesting tip from this queue job - CLIENT1\ProjConf 2 is the server name and the Project Server account (not user account but the "profile") used on that machine to make this queue request.

    So the queue shows something like this:-


    with the important fact that I didn't cancel anything and the save came from my client cache - and nothing was lost.

    Timesheet Update - Failed and Blocking Correlation

    This next example shows a couple of things - the sleeping state and that the retry does work.  As long as you fix the underlying problem.  The queue is all data driven and if the data stays the same then it will behave exactly the same.  (One definition of insanity is doing the same thing over and over expecting a different outcome - same thing with the queue).  If I submit a timesheet with administrative time then when the update is processed it puts a calendar exception in to my calendar for the non-working time.  If as a resource I am checked out then this update can go into a sleeping state (Waiting to be processed (Sleeping)) - and it wakes up every 2 minutes and tries again.  If I happen to get checked in in the meantime then all is good and the process completes.  If not then eventually it will fail.  The error shown in the queue even gives you a reasonable clue to why it failed (if you know the secret language - CICO = Check-in check-out):-

    Error summary/areas:
    Error details:

    <?xml version="1.0" encoding="utf-16"?>
      <array name="Array" type="System.Guid">
        <item value="079d778a-2a14-455a-a52e-3141b57e75ea">
          <error id="10101" name="CICOAlreadyCheckedOutToYou" uid="ce366c36-421b-4c47-8fa0-d68f42ba63d6" />
        <class name="Queue">
          <error id="26000" name="GeneralQueueJobFailed" uid="385171b0-3ee9-4087-b308-859cb62fea53" JobUID="702e81a6-4f0e-4faf-ab78-2ab81fe60972" ComputerName="SERVER2" GroupType="TimesheetUpdate" MessageType="UpdateTimesheetMessage" MessageId="1" Stage="" />

    To recover from this error you do not need to cancel - just fix the underlying problem, which in this case was that I had my account open in Manage Users on another IE session, and then select the job and click retry.


    This time it all works fine - even the blocked jobs can continue and other related jobs get spawned to update the reporting DB.


    So please, please, please - deleting the local cache and canceling queue jobs should be a last resort.  There is usually a better way.

    Technorati Tags: Project Server 2007

  • Brian Smith's Microsoft Project Support Blog

    Project Conference Day 1


    I had a chance to meet plenty of old friends today - and also put faces to names I have known for a while.  It was good to meet all of you.  Several people asked about the PowerPoint presentation so I have attached it to this posting.  If I get time I may record several of the demos and post them up to MSN Soapbox.  For now the pptx on it's own at least has many of the links you should find useful.  For those who were not able to be here the session was titled Administration of an EPM Solution and concentrated on Project Server 2007.

  • Brian Smith's Microsoft Project Support Blog

    Custom Fields and Lookup Tables


    In Project Server 2007 the structure of the custom fields has changed and this is causing some confusion both for developers and for users.  This isn't helped by our UI for creating and maintaining lookup tables.  In this posting I intend to give some background information about the structure - along with some of the potentially unexpected things that can happen if you don't understand this structure. 

    Custom fields can either be free form (of various datatypes) or based on pre-configured lookup tables.  The latter are the replacement for Outline Codes and it is these that can feed through to the cube as dimensions.  Numeric custom fields can go to the cube as measures.  The custom field when applied to a project (or task, or resource) has a value.  This value may be obvious - such as the actual text entered as the custom field for that project, or may be more obscure - such as a GUID that references a specific value from a lookup table.  It is this last variation I will drill into.

    Suppose we want to identify a color as our custom field and have a lookup table with a hierarchy something like this:-

    Light Red 23a6b3f1-80f3-4e0b-a494-00881571f4c1
      Green f3b5f8fc-1fce-4323-a84d-17f16408396e
      Blue 887c1fa3-d1c2-4b3a-a661-1a417b8b3d1e
    Dark Red 4049aa5f-c030-4dda-98cf-1a4d18fa0b94
      Green c3e5a030-925a-41eb-873f-2af26bd0c4a0
      Blue 95986ff0-d3a0-4ccc-bfae-2afc6bfb80e7

    So we can have Light, Blue - or Dark, Blue - or any of the other combinations.  In our UI for the lookup table each of the final leaves is identified by a GUID, and when you select Dark, Green this value in my example is c3e5a030-925a-41eb-873f-2af26bd0c4a0.  So if we use this lookup table value as a custom field then behind the scenes we would identify the actual selected value using this GUID.  Now suppose in the PWA UI for managing the lookup tables we made some changes and moved the structure around so it looked like this:-

    Light Red 23a6b3f1-80f3-4e0b-a494-00881571f4c1
      Green c3e5a030-925a-41eb-873f-2af26bd0c4a0
      Blue 887c1fa3-d1c2-4b3a-a661-1a417b8b3d1e
    Dark Red 4049aa5f-c030-4dda-98cf-1a4d18fa0b94
      Green f3b5f8fc-1fce-4323-a84d-17f16408396e
      Blue 95986ff0-d3a0-4ccc-bfae-2afc6bfb80e7

    So what changed?  Well we didn't mean for anything to change - and in the UI it looks exactly the same as when we started (as we don't see the GUIDS) - but we accidentally moved the Green that was under Dark to be under Light - and the Green that was under Light is now under Dark.  The problem is that as far as our project (or task or resource) is concerned it is associated with the GUID c3e5... so it now relates to Light, Green which probably isn't what we intended.

    So please take care when maintaining the lookup tables - don't move items between the different parts of the hierarchy unless this is really what you want.

    I will follow this up with more detail on setting custom fields through the PSI - but if you can't wait Christophe did a posting similar to this recently on resource custom fields.  I'll elaborate to help you understand the different types of fields and the different values you will need to set.

    Technorati Tags: Project Server 2007

  • Brian Smith's Microsoft Project Support Blog

    Backup and Restore - what are the options?


    A while back I posted about the backup and restore process and recommended using the SharePoint utility in Central Administration.  That is still my preference and I recently used this to rebuild my server farm including 3 servers, 2 shared service providers and a dozen PWA instances covering about 10 different languages.  I must admit it did take several attempts and my one big take-away is - try, try, try again until it all works in one go.  The process isn't very forgiving if one piece fails, so best to remedy the problem and run the whole restore again.  The problems I ran into were services that needed starting (Office search in my case, and the Project application) or databases I'd forgotten to delete.  Top tip number two is that if you have a complex restore to do and you are changing machine names, URLs, paths to db files, service accounts etc. it can be very tedious to edit these in the UI each time - particularly for all 12 PWA sites!  So a short cut is first to take a backup of the spbackup.xml file, and then edit the original and change the various things that need changing in the XML file.  It should be easy to work out what needs editing - especially if you have already been through the restore process a couple of times.

    So this restore gets everything back how it was, on either the same server or a different configuration. 

    What if you only have database backups?  What can you do and what do you lose?  This is a reasonably common scenario - particularly for people with a 2003 background who are used to working with just the WSS content db and the ProjectServer db.  But things have changed - and the main challenge is that the PWA site is now held within the content database.  This causes some issues as if you want to move the databases to another server you will need to re-provision this PWA site - and you can't do this against the same content db if it already exists.  And if you delete it then everything under /PWA is gone!  Which may well include any workspaces for your projects as this is the default location for them.  So you have a few options, and we will assume here that you are restoring and re-attaching your main content database to the default web site, and restoring your 4 project server databases.

    1. Re-provision your /PWA site to a different location - such as /PWA2, and all your old content will still be under /PWA and you will still be able to get to it.  This is a good way to work going forward as you now have two independent locations for your PWA site and your other WSS content.
    2. Don't attach the original content db and provision your PWA site to /PWA, then attach your content db to another web application (different port).  You can then re-link your projects to point to the different location for the content.
    3. An extension of option 2 - you could use stsadm export and import functions to copy the individual workspaces from their new location back under /PWA - and all is back how it was - well almost.

    So what do you still lose by going this route?  Fidelity.  If you have customized your PWA site through site settings you will lose these changes using any method other than the SharePoint backup/restore.  As an example the following site was replicated using option 1 and the highlighted changes (and the colour from the theme) are lost in the restored site pictured below.  You do however keep any changes made through Server Settings such as menu edits (My Timecard in place of My Timesheets in the screen shots).

    Original PWA Site

    Restored PWA Site

    I hope this helps understand what is stored where - and the challenges of moving data between servers, either for migration or disaster recovery.

    Technorati Tags: Project Server 2007

Page 85 of 98 (489 items) «8384858687»