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

  • Brian Smith's Microsoft Project Support Blog

    Project Server 2010: Orphan baselines breaking the reporting publish

    • 20 Comments

    *** Update 7/31/2013 - This was fixed in the June 2012 Cumulative Update for Project 2010 – and was a client fix - http://support.microsoft.com/kb/2598351.  Customers can load the latest Cumulative Update for Project 2010 and it will include this fix.  The fix will stop the problem happening again but does not fix up any existing issues – so they may need to clean up the database using the guidance below.  If you need help then open a support incident with us – we do not charge for bug related incidents – so we can help you through this.  You should also use the feature on the server to control which patch level  of Project 2010 can connect to Project Server to ensure that this problem is not re-introduced by a user with an unpatched version of Project 2010. ***

     

     

    This problem has been around for a while and I know some customers were running into it very soon after the release, but we had been struggling to get a repro and understand exactly what was causing it.  We now understand the root cause and have a fix coming hopefully in the June 2012 Cumulative Update for Project Professional 2010 (no promises – but that is the current target) and there are some ways of working that can limit your chances of running into this – so decided we should share this to avoid continued inconvenience until we get the fix out there.

    First lets take a look at the symptoms.  The most usual indication of the problem, as the title suggests, is orphan baseline values leading to the error when publishing – a Failed But Not Blocking Correlation problem on a Reporting (Project Publish) job that will show several of the following errors if you click through for the error details:

    ReportingProjectChangeMessageFailed (24006) - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "ProjectServer_Reporting", table "dbo.MSP_EpmTask". The statement has been terminated..

    GeneralQueueJobFailed (26000) - ReportingProjectPublish.ReportProjectPublishMessageEx

    These failures are for the reporting job – so will mean that reports based on the reporting database, and any fresh OLAP cube builds could be missing data.

    Sometimes there may also be a crash on saving, either with a fairly generic MSSOAP 16 Send Incomplete error from Project Professional 2010 (though a subsequent save will work fine), or from PWA a queue error -

    GeneralQueueException (9131) A Project Operation failed due to a Queue Exception. Sub Job ID is: . Exception details are: System.NullReferenceException: …at Microsoft.Office.Project.DataEdit.Assignments.AssignmentCalendarUpdateHelper.ConvertActualContourToElapsed(,,,

    There may then be issues with users accessing timesheets – The view failed to load.  Press OK to reload this view… (and OK will not help).

    image

    The error that will be found in the ULS logs will refer to a Calendar whose UID cannot be found…

    Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Statusing.StatusingGetMyWorkForGridJson System.InvalidOperationException: CacheProjectBaseCalendars could not find project calendar for project. CalUid=0c13de33-2a07-4310-b091-c77990d9dd6a   

    The root of all these issues is that when you use any of the Save & Send options (XML, CSV, Excel etc.) that we are incorrectly changing some of the GUIDs associated with entities such as the tasks and calendars.  Now this isn’t affecting the main tasks and assignment GUIDs as these bad values are not persisted back to the database – but we do however create a new baseline for these non-existent new task GUIDs, and can also save a bad calendar GUID – which leads to the Timesheet problem.

    First the best way to avoid this issue, and then on to the detection and clean up at the database level.

    If you do need to use Save & Send then the best practice until we release the fix for this is to first save the plan to the server, and publish if you need to.  Then do whatever you need to with Save & Send, and then immediately after this – close and check in the plan – but do not re-save to the server.  Discard changes if it asks – but of course you will have needed to save BEFORE you did the Save & Send (just making sure you are paying attention) to avoid losing any changes you really needed.  As the bad stuff will also get persisted to the local cache, this is one of those rare occasions when you will find me suggesting that the project is removed from the local cache – after ensuring that the save and check-in completed successfully.

    WARNING – the following steps are direct queries against the Project Server databases – please be sure you are working against the right databases when using these – and have a database backup should any problems occur.

    The detection of this condition is pretty straightforward, as we are just looking for baselines that exist for a task that does not exist, so the following query executed against the Draft database will do this (Change the name to match your specific DBs – the default ProjectServer_ names are used below:

    -- Detect for orphan baseline task records that can cause reporting publish job failures.

    USE ProjectServer_Draft -- specify the appropriate draft database

    select PROJ_NAME, MTB.PROJ_UID,TASK_UID,TB_BASE_NUM from MSP_TASK_BASELINES MTB
    inner join MSP_PROJECTS MP on MTB.proj_uid=MP.proj_uid
    where TASK_UID not in (select TASK_UID from MSP_TASKS)

    This will return rows if the condition exists – and identify which projects – as before clean-up you will probably want to get them removed from the PM’s local cache as otherwise they could be re-introduced.

    The next scripts do the cleaning up in the DB, and they are simply deleting baseline records where the tasks are non-existent.

    -- Script to run on the draft DB
    USE ProjectServer_Draft -- specify the appropriate draft database

    delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

    -- Script to run on the published DB
    USE ProjectServer_Published -- specify the appropriate published database

    delete from MSP_TASK_BASELINES where TASK_UID not in (select TASK_UID from MSP_TASKS)

    I hope this helps to understand the nature of the issue and ways to avoid it until the fix comes along.  Our apologies for the inconvenience I know this has caused many of our customers – and hopefully for those who have needed to re-run the clean-up scripts regularly this may give a way to reduce the pain.

    If you need any assistance with these steps then feel free to open a support incident – and when I say free I mean free – this is a bug and we do not charge for incidents that are due to bugs (or we will refund – which amounts to the same thing).

    The ULS log entry associated with the initial Queue errors above (for the benefit of the search engines):

    05/01/2012 11:57:55.67    Microsoft.Office.Project.Server (0x1D74)    0x335C    Project Server    Reporting    atwj    Critical    Standard Information:PSI Entry Point:   Project User: REDMOND\brismith  Correlation Id: e1f4e953-7dea-448a-a528-709075c698bf  PWA Site URL: http://brismith8100/PWA  SSP Name: Project Server Service Application  PSError: ReportingProjectChangeMessageFailed (24006) RDS: The request to synchronize change(s) to project Project UID='216733b0-e194-469a-afc3-9235da4ce4c1'. PublishType='ProjectPublish' failed.  Message: 'ReportingProjectChangeMessageFailed'. Message Body: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmTaskBaseline_ProjectUID_TaskUID". The conflict occurred in database "ProjectServer_Reporting", table "dbo.MSP_EpmTask".  The statement has been terminated. Error:(null)    e1f4e953-7dea-448a-a528-709075c698bf

    and for the Timesheet error:

    05/01/2012 12:13:29.65    w3wp.exe (0x2444)    0x23D8    Project Server    Task Statusing and Updates    btw9    High    CacheProjectBaseCalendars: could not locate data for calendar 0c13de33-2a07-4310-b091-c77990d9dd6a for project 216733b0-e194-469a-afc3-9235da4ce4c1    e5dd4eaf-551a-469b-a3e0-1f60e2f3d1af

    05/01/2012 12:13:29.85    w3wp.exe (0x2444)    0x23D8    Project Server    General    0000    Exception    Exception occurred in method Microsoft.Office.Project.Server.BusinessLayer.Statusing.StatusingGetMyWorkForGridJson System.InvalidOperationException: CacheProjectBaseCalendars could not find project calendar for project. CalUid=0c13de33-2a07-4310-b091-c77990d9dd6a     at Microsoft.Office.Project.Server.BusinessLayer.TimePhasedDataAccess.CacheProjectBaseCalendars()     at Microsoft.Office.Project.Server.BusinessLayer.TimePhasedDataAccess..ctor(StatusingPageLoadDataSet dataset)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.ReadStatusTimephasedDataForResource(IList`1 gridChanges, Guid[] vAssnUids, IDictionary`2 assn2proj, StatusingTimephasedPeriod[] tpdPeriods, DateTime tpStart, DateTime tpEnd)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.<>c__DisplayClass57.<CreateTimephasedDataColumnFiller>b__56(IEnumerable`1 Keys)     at Microsoft.SharePoint.JSGrid.GridSerializer.BuildOutput()     at Microsoft.SharePoint.JSGrid.GridSerializer.ToJson(Serializer s)     at Microsoft.SharePoint.JsonUtilities.Serializer.SerializeToJson(Object o)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.GetMyWorkForGridJson(JsGridSerializerArguments gridSerializerArgs, String gridChangesJson, String projectAssignmentsMap, Guid viewUid, String timephasedStart, String timephasedEnd, Byte pane, Int32 durationType, Int32 workType, Int32 dateFormat, Boolean clearPersistedProperties, Nullable`1 rowFilterType)     at Microsoft.Office.Project.Server.Wcf.Implementation.PWAImpl.StatusingGetMyWorkForGridJson(JsGridSerializerArguments gridSerializerArgs, String gridChangesJson, String projectAssignmentsMap, Guid viewUid, String timephasedStart, String timephasedEnd, Byte pane, Int32 durationType, Int32 workType, Int32 dateFormat, Boolean clearPersistedProperties, Nullable`1 rowFilterType)    e5dd4eaf-551a-469b-a3e0-1f60e2f3d1af

  • Brian Smith's Microsoft Project Support Blog

    Microsoft Office Project Server 2007 and SQL Server 2005 Analysis Services - Building Cubes

    • 57 Comments

    Following on from my permissions piece with Project Server I will extend this logic to the service accounts and permissions to get a successful cube build.  I'll start with an explanation of what goes on when building cubes which should help any troubleshooting you do.

    When you click on Build Cube then this kicks of a sequence of events starting with the save of any new or changed data in the cube settings - such as the server or cube name.  Next a job will be placed on the Project Server queue requesting a cube build.  This job will be picked off the queue and processed by the Microsoft.Office.Project.Server.Queuing.exe process, which will spawn the ProjectServerOLAPCubeGenerator.exe process. Both of these processes will be running under the identity of the admin account of the SSP - in my last posting this is the SSPAdmin.  So this user needs to be an admin within Analysis Services so it can communicate through DSO to Analysis Services.  This permission is added through a SQL Management Studio connection to Analysis Services by right-clicking the instance name and then selecting Properties, selecting the Security tab and then adding the user (a restart of the Analysis Services service at this point will also unsure the running instance is aware of the permission change) .  This process also needs to access the repository of meta data used to define the cubes.  This repository is detailed in KB 921116 (as are some other pre-requisites for multi server environments) and is in a share on the Analysis Services server called MSOLAPRepository$.  If you have a single server then the share will not be used - instead the direct directory location of C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\DSO9. (The X will be a number relating to the installation of analysis services).  Therefore SSPAdmin, or your equivalent service account will need read and write access to this directory - and if you are in a multi server environment then also read/write access via the share.

    The next activity in the cube building process is the Analysis Services executable - MSMDSRV.exe -  actually building the cube based on the instructions given by the ProjectServerOLAPCubeGenerator.exe process.  This executable runs under the identity of the account running the SQL Server Analysis Services (MSSQLSERVER) (or named instance) service.  I'll refer to this account as ASAdmin  So this account needs to be able to read the reporting database of the Project Server instance, which is in effect the staging tables for the cubes.  Adding a login to SQL Server for ASAdmin with datareader role on ProjectServer_Reporting (or whatever reporting database name you are using) achieves this.  That should be all you need to get a cube building.

    So basically the SSPAdmin needs to be an admin in Analysis Services with read/write access to the repository.  ASAdmin needs datareader access to the reporting database.

    Also remember - when building a cube your application server is talking to/from your SQL Server Analysis Services server - when viewing or building views in Project Web Access your client PC is talking directly to your Analysis Services server (and each client needs the ASOLEDB 9.0 components).  Make sure any firewalls allow for this traffic.

    The default instance of Analysis Services will normally be listening on port 2383.  If you have named instances then the SQL Browser service will need to be running on the server to tell give clients a port for the named instance.  The SQL Browser is normally on port 2382.

    Named instances of Analysis Services will have other dynamically allocated ports. These can be discovered by looking in the configuration file for SQL Server Browser.  Open the msmdredir.ini file located at %Program files%\Microsoft SQL Server\90\Shared\ASConfig and look at the <Instances> section in it.  On 64 bit machines this may be in the Program Files (x86) directory.
    Here is an example:-

    <Instances>
         <Instance>
             <Name>AS2005</Name>
             <Port>1259</Port>
         </Instance>
     </Instances>

    and would mean your AS2005 instance is listening on port 1259.

    For my next post, rather than cluttering this one, I will show a variety of the errors from both ULS logs and Event logs that can appear if the above settings are not in place.

    Technorati Tags:

  • Brian Smith's Microsoft Project Support Blog

    Waiting for Resources? - What happens when you provision a new Project Web Access site

    • 46 Comments

    One problem I have seen a few times is the Project Web Access site provisioning running into issues.  If it fails completely then the error messages are pretty good - and you can generally resolve the issue and re-try and everything will be good.  However, what do you do when it just sits on "Waiting for Resources" and nothing happens?  The quick answer is that this relies on the SharePoint Timer Service and a couple of Shared Service Provider services that can be viewed through Timer Job Definitions (Shared Services Timer Job and Project Server Synchronizing Job for 'SharedServices1' - or whatever yours is called).  If these are not running then you will be "Waiting for resources" for a very long time!  Also there could be multiple versions of the timer jobs if you have multiple SSPs so it can get confusing, particularly as the first one does not differentiate by name.  (Clue - the JobId in the URL for the job is the Id of the timer job row in the Objects table in SharePoint_Config database, and the Properties column from this row will lead you to the Guid of the TargetSharedResourceProvider - which will be the Id of the Shared Services Provider also in the same table.)

    So to dig a little deeper so you can understand where it might be stuck I'll explain what is going on in the background which hopefully will help you find what is stopping it from working.

    So, step 1 - you have been on the Create a Project Web Access Site page (CreatePWA.aspx) and entered all the details, and it goes back to the Manage Project Web Access Sites (ManagePWA.aspx) page and just sits there.  At that point a row has been added to the MIPScheduledJob table in the SharedService1_DB (your database name may vary - this is the default).  This is a pre-synchronizing job for the site, and is added to the database by the account running the Shared Services Provider application pool.

    Step 2.  The Shared Service Timer Job picks up the row from this database table and adds a row to the SharePoint_Config database Objects table.  In the properties field of this table is some XML describing the site to be built, database names etc.  This timer job will run as the account of the farm administrator (i.e. the account running the OWSTIMER service)

    Step 3.  The Project Server Synchronizing Job for 'SharedServices1' sees this row and actions it by creating the site, creating the database and then configuring the instance of PWA.  If everything is working you see the status change on the ManagePWA page as these different stages are processed, and finally it will say Provisioned!  Again, this timer service runs as the farm administrator.

    Waiting for resources will be seen until the early stages of step 3.

    So check all your services are running and your timer jobs are present and enabled and all should be good.  One other workaround that generally gets things moving again is to create  a new Shared Services Provider - which will then create new timer jobs and overcome any underlying issues.  The web applications can then be associated with the new SSP, the new SSP can be made the default if you are not using the old one for anything else, and the old one could be deleted.

    I mention the databases here on the condition that you can look - but don't touch! 

    Technorati Tags: Project Server 2007

  • Brian Smith's Microsoft Project Support Blog

    How to check a project in through the database

    • 35 Comments

    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

  • Brian Smith's Microsoft Project Support Blog

    To Add or Update - That is the Question. The PSI and custom fields - Updated

    • 49 Comments

    This posting was inspired by a support call concerning custom fields - but does apply to other entities in the Project dataset and others.  The question was around the QueueUpdateProject and QueueAddToProject web services calls that are part of the Project web service in the PSI.  If you use the wrong call then the error message is usually helpful telling you you can't add with the update call and vice versa.  But the thing that does catch people out are the custom fields... 

    By default a project dataset will not contain any rows in the ProjectCustomFields data table unless they have either been set (through PWA, Project Professional or the PSI) or they contain formulae, or are based on a lookup table with a default value defined.  So for a freshly created project (from Project Professional) when looking at Project Information it appears that all custom fields are there - and some may even look to have values (number fields will show 0) whereas in fact once saved there are no rows in the dataset for project custom fields (except as mentioned above - formulae based, and default lookup tables values).  So if you want to set a custom field via the PSI your code should check if there is a row already present for that custom field (identify by the MD_PROP_UID or MD_PROP_ID) and if it is there then you can change the value and use QueueUpdateProject - and if it doesn't exist then you will need to create a new blank project dataset and add a new project custom field row and set the values (including a new GUID for CUSTOM_FIELD_UID, the projects PROJ_UID and the MD_PROP_UID for the field you want to set) then use the QueueAddToProject.

    (Update - Although this logic holds true for adding/updating tasks to a Project dataset you can in fact ADD a new custom field row to an existing project dataset and use the QueueUpdateProject method - and it will work.  You do not need to use the QueueAddToProject method - although this will work too, assuming it only contains the additional custom field row!  Thanks to Martin for pointing this out - through experience.)

    Just to confuse the situation we have a bug currently when using the PSI or creating an Activity Plan/Proposal that can duplicate custom field rows.  If you have any project level custom fields that use graphical indicators but do not have formulae then you can get duplicate custom field rows created for each of them.  I'm not sure yet if the bug is they get created at all (as they do not get created in Project Professional projects) or that they appear twice. I will post an update when I have more information.  For now a quick workaround is to not use graphical indicators for this type of field.  Cleaning up existing ones may be a little more challenging and I would suggest a support call.  Although this causes problems for the PSI when updating these duplicated rows (so this affects changes for Activity Plans , Proposals and use of the Edit Project Properties in PWA) you can still make changes in Project Professional through the Project Information dialog.

    The errors you might see - (these are generic so you could see these errors for other root causes):-

    When updating a custom field for an Activity Plan or Proposal you will get an error dialog box:-

    Windows Internet Explorer
    An error has occurred when saving your project to the server. Please contact your system administrator for assistance.
    [GeneralUnhandledException]

    When updating a custom field through Project Center's Edit Project Properties option the error will display at the top of the page in red:-

    The project could not be saved due to the following reason(s):

    An unknown error has occured (occurred is mis-spelled in the error).

    When updating through the PSI this will result in a GeneralUnhandledException.

    One final point - setting or updating a project custom field for an existing Project Professional created project through the PSI or Edit Project Properties will also introduce this problem.  The extra duplicates will get added as well as the custom field you are setting.

Page 4 of 96 (476 items) «23456»