We don't seem to have done a good job in educating our customers on this topic, so thought I'd put this out there at least as a starting point and I'm sure we can also try and get better samples in the SDK.
One of the tricky things with setting custom fields for any entity (Project, Resource or Task - but also assignment or timesheet) is that you cannot always just call up the dataset, navigate to the custom field row and set the value. In many cases the row may not yet exist, so you need to add it, then set all the required properties and then update back through the PSI.
As an example if you create a project through the PSI it will have no project level custom field rows created by default, (except ones based on Lookup Tables with a default value forced) and any tasks will only have the "Health" special custom field set, and again- any with Lookup Table defaults. If you then open in Project Professional and just save again, you will then have added any calculated fields at the project level, along with any calculated fields at the task level. Also at this stage the project summary task (Task 0) is populated with any task level fields with roll up to summary level set.
As my server does not have all the possible combinations of fields I may be missing something here - but ProjTool is a great way to see just what is in the dataset at any time.
So whenever you are setting custom fields first see if the row already exists - and if not you will need to add a customfieldrow to whatever dataset you are working with. Then the next step is to set the appropriate properties - which will depend on the type of field you are setting (defined by FIELD_TYPE_ENUM), and the dataset you are adding it to. For instance a ProjectCustomFieldsRow will need the PROJ_UID. A TaskCustomFieldsRow will need the PROJ_UID and the TASK_UID. A Timesheet CustomFieldsRow will need the TS_UID to identify the timesheet and a TS_LINE_UID to identify the specific line. Then for any row you need to set a new GUID for the CUSTOM_FIELD_UID, the MD_PROP_UID and/or the MD_PROP_ID to identify the specific custom field and the actual value you want to set in the appropriate property as detailed in the following table.
Set field value in:
Value in 1/COST_MULTIPLIER dollars
A date value. HIWORD contains days off set from 1/1/84. LOWORD contains minute off-set, ranging from 0 to 1440, from 12:00 A.M. (midnight)
Value in 1/DUR_MULTIPLIER minutes
DUR_VALUE and DUR_FMT
A string value
Index into yes/no string table
A number value
FINISHDATE is also listed in the SDK but is not applicable to these custom fields. Lookup Table isn't specifically a custom field type - but I added it for completeness. If the custom field is based on a Lookup Table then the LT_STRUCT_UID of the specific entry in the Lookup Table is entered in the CODE_VALUE property. For those that accept multiple values there will be a row with each CODE_VALUE - not a single row with multiple CODE_VALUES.
Another property you will come across in the datasets is the INDICATOR_VALUE - which will hold the enumeration value for the indicator to be displayed based on the custom field settings.
No code sample just yet on this - but I will try and come up with some shortly. I am looking into a support incident where values are being set for summary tasks (or rather they are not) - these are likely to be read-only if "roll-up" is set, so be aware of that.
*** Update *** Finally got a sample together - hope it is worth the wait - http://blogs.msdn.com/b/brismith/archive/2010/08/25/project-server-adding-custom-fields-to-projects-and-tasks-using-the-psi.aspx
The ProjCFDlg.cs sample in ProjTool and the Add Project Custom Field option on the Project Details pane of ProjTool is a great place to look for an example that covers this well.
Other things to be aware of are that constraints applied to the custom fields will need to be adhered to. If the custom field only allows selection of codes with no subordinate value (leaf nodes) then the PSI cannot over-ride this.
Just a quick posting on this topic pointing out a few of the changes between 2007 and 2010. We have some documents already up on TechNet – under the Operations section at http://technet.microsoft.com/en-us/library/cc197578.aspx, but thought this would be a useful posting to make clear what you can and cannot do.
In 2007 you could restore your 4 Project Server databases, then point your provision job at these 4 and you’d have a new PWA instance using those DBs. If you wanted your workspaces too then you’d need to copy the sites or if you’d had the forethought to use a different web application for sites then it could be even easier.
In 2010 we introduce the 5 dB restore –so you can now restore your Content database containing your PWA site (and potentially all your Project sites (workspaces,in 2007 speak), and your 4 Project Server databases, attach the Content DB to a web application and then you can provision and it will use this PWA site and the databases – rather than complaining that the /PWA site already exists – which was the case in 2007. So this is excellent – and makes it much easier to move a complete copy of the instance around.
However, there are some gotchas:
You cannot change the name of the site - but you could restore to a different web application (port) to avoid a clash if you needed to pull /PWA from one server to another server where /PWA already existed
If you change domains you might have some unexpected results – This is due to differences in accounts in different domains, and will be even more unusual if you happen to use a different farm admin from the Project admin (which would be best practice, but in a dev/test/support scenario might not be the case. The issue here is that attaching the content DB will add the farm admin as the site administrator, but will not add the Project admin when you provision the site. So you have a great Catch 22. Once you provision the PWA instance the Project admin will get an access denied on /PWA as he has no rights to the site. Farm Admin will get Access Denied (or might be unexpected error) as he is not a project server user. Simple solution is to navigate to http://servername/pwa/-layouts/user.aspx as the Farm Admin and then give the Project admin rights. Once you can connect as Project Admin then you can update users credentials as necessary.
4 DB restores will NOT work – it looks like it has worked, but if you try to drill in to a project you will see a message “Unable to open Project, no valid Project Detail Page could be found for the project.” The Project Detail Pages (PDPs) are stored in the Content DB – if you don’t; restore the Content DB then you don’t have them and cannot get to existing Projects through Project Center. You can however still access via Project Professional, so in some support or disaster recovery scenarios this might be good enough – just to get access to projects while the full recovery happens somewhere else.
You may need to use the WSS site re-linker tool – now built in to Server Settings in PWA as “Bulk Update Project Sites”. If you have changed URL die to a different port then re-linking should get your sites in order. In upgrade scenarios the “Previous Site Path” Web application might show as a GUID in the drop down – however it still works and will re-link the sites.
Cannot attach Content DB via the Central Administration UI – This is handled with very good error messages and will tell you to use stsadm if it needs to upgrade the content database due to differences of versions. You could also use the PowerShell command – mount-SPContentDatabase. The correct syntax of the stsadm command is:
stsadm –o addcontentdb –url “http;//servername:port/ –databasename Content_DB_Name
stsadm –o addcontentdb –url “http;//servername:port/ –databasename Content_DB_Name
Partial Restores using Full Farm Backup are not supported. From the UI it looks as though you should be able to select just a PWA instance and backup stuff and then restore from this backup to another farm. This isn’t supported. it will only work when restoring to the same farm and the same named /PWA site. I guess it could be used as a means of restoring the required databases, but you would still need to provision a site on the target server against the databases to get anything to work.
I am sure we will find more gotchas as we go along, and perhaps even some workarounds to recover from certain issues – but that’s all for now. Let me know of anything you feel should be added. I will probably do a more “step by step” posting with a few more details – but the TechNet articles cover those kind of details pretty well.
I’ll get this information into a KB article too, and I’ll also try and keep this posting up to date as we release each cumulative update or service pack for Project Server 2010 and Project 2010. If you are not reading this at http://blogs.msdn.com/b/brismith then you may be reading out of date information!
The version can be different depending where you look, so I will tabulate the version you will see in Control Panel, Programs and Features (binary version) or on the individual binaries – which is also listed in Central Administration under Upgrade and Migration, Check Product and Patch Installation Status – and also the version you will see in the databases. I’ll mention too various other components that might be of interest. I have listed just the Project Server KB for the CU – but the DB Version noted for SharePoint assumes that either a SharePoint Server or Server rollup has also been installed.
As a reminder to get the version from the database there is a table in each SharePoint DB, and each Project Server DB called Versions so a query such as the following will return the current version, which will be the highest Version next to the NULL GUID.
SELECT * FROM Versions
WHERE VersionId ='00000000-0000-0000-0000-000000000000'
SELECT * FROM Versions
WHERE VersionId ='00000000-0000-0000-0000-000000000000'
Project - 14.0.5130.500
February 2011 CU
14.0.5136.5000 See KB for individual binary versions. KB will show in installed updates
April 2011 CU
14.0.5138.5000See KB for individual binary versions. KB will show in installed updates
June 2011 CU
14.0.6106.5002 See KB for individual binary versions. KB will show in installed updates
If you load Service Pack 1 for Project Server 2010 then you will see Versions of 14.0.6027.1000 for Project and 14.0.6029.1000 for SharePoint. If you loaded June CU at the same time and did not run the SharePoint Configuration Wizard between loading the SP and the CU then you will just see the latest - the 6105.5000 and 6106.5002 from the table above.
Details from Control Panel, Programs and Features, and also the Backstage (File, Help tab), both the main version displayed, and the additional version information.
KB 2413663 will show in installed updates. Backstage 14.0.5128.5000. Under Additional Versions and Copyright Information – Microsoft Project 2010 (14.0.5126.5000) MSO(14.0.5128.5000) MSO may be different depending on other Office KBs installed
June 2010 - http://blogs.technet.com/b/projectadministration/archive/2010/07/22/microsoft-project-server-and-sharepoint-2010-june-cu-2010-are-live.aspx
August 2010 - http://blogs.technet.com/b/projectadministration/archive/2010/09/02/microsoft-project-server-and-sharepoint-2007-and-2010-august-cu-2010-are-live.aspx
October CU - http://blogs.technet.com/b/projectadministration/archive/2010/10/27/microsoft-project-server-and-sharepoint-2007-and-2010-october-cu-2010-are-live.aspx
December CU - http://blogs.technet.com/b/projectadministration/archive/2010/12/15/microsoft-project-server-and-sharepoint-2007-and-2010-december-cu-2010-are-mostly-live.aspx
February 2011 CU - http://blogs.technet.com/b/projectadministration/archive/2011/02/24/microsoft-project-server-and-sharepoint-2007-and-2010-february-cu-2011-are-live.aspx
April 2011 CU - http://blogs.technet.com/b/projectadministration/archive/2011/04/29/microsoft-project-server-and-sharepoint-2007-and-2010-april-cu-2011-are-live.aspx
SP1 - http://blogs.msdn.com/b/project/archive/2011/06/28/announcing-the-release-of-service-pack-1-sp1-for-microsoft-project-and-project-server-2010.aspx
June 2011 CU - http://blogs.technet.com/b/projectadministration/archive/2011/08/22/microsoft-project-server-and-sharepoint-2007-and-2010-june-cu-2011-announcement.aspx
Links to all the Cumulative Update Webcasts, and other great update links can be found at http://technet.microsoft.com/en-US/projectserver/gg176680.aspx
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: Project Server 2007
I was going to use the title asking ‘why are people still deleting the cache?’ until my colleague Corrie came up with this much better one! Rather than asking why you are still doing it – I am telling you not to!
I know there is a lot of history behind this one, and for those of you that used Project Server 2007 in its early days there were some challenges such as the ‘check-in pending’ saga that got people in to the habit of deleting the project cache. We fixed the problem, then we fixed it again (and again) and you should not generally be seeing any issues with leaving your cache alone to do its job. However, many customers I talk to are routinely deleting the project from the local cache before they open it and then again after they close it! Why!?! Its job is an important one – it saves you having to pull that data from the server again – which will reduce network traffic, the hit on both the web services and the database, which means they can be getting on and doing useful stuff.
I’ll also address a miss-conception misconception (thank you Trevor - not addressing the misconception that I can spell...) here that I have heard from a number of customers – the choice of where to load the project from – cache or server? You don’t have a choice – Project will load it from the cache if it is there, and then load any incremental pieces it needs from the server, to get you the current version of that plan. In the screen shot below:
the line actually reads ‘Retrieve the list of all projects from Project Server’. It does not also read – ‘…and open any I might choose after clicking this link from the server and ignore the local cache’. You don’t get the choice and you don’t need to choose.
I’m sure many of you will not have read this far before clicking the comments option to tell me of all the problems you are having.(and I’m sure some of you are still having problems). First check that you have the latest cumulative updates and service packs. If you are still really having issues unless you delete the local cached copy then we certainly need to hear about it so we can fix the problem rather than have you waste your time and system resources doing things that you should not need to do.
I will admit that there can be times when as support engineers we will ask you to remove your local cache to troubleshoot specific scenarios. The cache itself also has intelligence that allows it to decide that it may have some bad stuff – and it will get a new clean copy of data from the server (symptom of this will be several files in the cache directory with 1,2,3 etc. at the end). There have also been a few bugs we have worked on recently which ONLY surface when the user has cleared their cache!
So please, if you have been deleting your cache as a matter of routine, then either stop – or speak to your PMO or IT people and ask why they have you do this – and if we need to fix something else then we can take a look.