Customise #Project Professional 2010 installation #ProjectServer #PS2010 #MSProject #Office2010

July 29, 2011 at 11:58 am | Posted in Installation | 9 Comments
Tags: , , ,

When deploying client applications within an organisation this needs to be as simple as possible. With the use of the Office 2010 Customisation Tool, deploying Project Professional 2010 can be made very simple. This blog post explains how to configure your Project Professional 2010 installation media to automatically set certain settings during the installation. For the purpose of this blog post the following settings will be configured in the install media:

  • default start time
  • default finish time
  • working hours per day
  • working hours per week
  • enable VBA  macros
  • automatically set the server connection

All other project options are available if needed.

To access the Office Customisation Tool run the Project Professional 2010 setup.exe with the /Admin switch from the Run box, example displayed below:

E:\Software\Project Professional 2010\Setup.exe /Admin


Click OK the setup a new customisation file for Project Professional 2010:


Click “Install location and organisation name” from the menu then set the path and name as required:


Click “Licensing and user interface”, in this example we are using a KMS server but if you do not use a KMS server enter the product key. You can also set what will be displayed during the install, in this example we have set this to basic. This will show a welcome screen and progress bar. Also the three check boxes are checked so that the user will see a completion message, modals are suppressed and there is no option to cancel the install:


It is possible to remove or keep earlier versions using the “Remove previous installations” menu:


In this example we wanted to allow VBA macros, so the next menu we edit is the “Office security settings”, scroll down to Microsoft Project – VBA macro warning options, select this setting and click modify. Then select the No security checks for VBA macros option.


Next we want to set the default Project Calendar settings, to do this click “Modify user settings” expand out Microsoft Project 2010 > Project Options > Calendar. Double click each setting, enable it then set the required values, also uncheck the box “Migrate user settings”:


There is also another macro security option to configure, expand out security under Microsoft Project 2010 and click Tools | Macro, double click security level, check enabled, set the level and click OK:



Also in this example the project server connection information will automatically be set on install. This is done by importing a Registry Key that contains the profile details. The registry key can be found on a machine that already has Project 2010 installed and connect to the server, the path is HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\MS Project\Profiles\<Profile Name>. Export this key out, edit as required then import the reg key into the customisation file using the “Add registry entries” menu.

Once all of the changes have been made, click File > Save, give the file an appropriate name  such as customprojsettings.msp and save this in the Updates folder in the install media.

Look out for my post within the next week or so that will cover slipstreaming updates into the Project Professional 2010 install media as these two go hand in hand.

#ProjectServer 2010 Resource names dropdown menu issue when editing Projects in PWA #PS2010 #Project #EPM

July 27, 2011 at 5:58 pm | Posted in Issue | Comments Off on #ProjectServer 2010 Resource names dropdown menu issue when editing Projects in PWA #PS2010 #Project #EPM
Tags: ,

I recently came across an issue with the Resource Names dropdown menu when editing Projects in PWA with Internet Explorer 7.0. The issue is that when you click the resource names field for a task at the bottom of the IE window in PWA so that the menu opens upwards above the task you are not able to select the resource as the menu disappears when you hover over it. It works fine if you click the resource name field for a task at the top of the IE window as the menu will open downwards below the task. I am not able to replicate this behaviour in Internet Explorer 8.0 or 9.0. Below are some screen shots to show the error as the explanation is not that clear!

Firstly click in the Resource names cell at the bottom of IE to get the Resource Names menu to load upwards:


Then hover over the menu as though you were going to select a resource:


As you can see, the menu disappears.

This functionality works as expected when using Internet Explorer 8.0 and Internet Explorer 9.0.

#ProjectServer 2010 #Excel / Excel Services Report Authentication #PS2010 #EPM #SharePoint #SP2010

July 25, 2011 at 12:07 pm | Posted in Administration, Functionality, Installation | 11 Comments
Tags: , ,

Project Server 2010 uses Excel Services as the primary reporting technology, this post gives an overview on how users authenticate to each data source along with the errors seen if users / accounts don’t have access. There are two different data sources by default, the Reporting database and the OLAP Cube.

Authentication In Excel Client

When creating or editing Excel Services reports the credentials of the logged on user who launched Excel will be used to authenticate the user against the data source. Firstly we will look at the Reporting database reports. To read data from a SQL database the user will need Data Reader (db_datareader) permission against the database, in this case the PWA_Reporting database. The recommended approach from Microsoft is to create a new Active Directory Security Group for Report Authors and add all users who create / edit reports to the this group. This group is then added to the PWA_Reporting database with data reader permissions, this saves adding users directly to the database. Excel will connect directly to the PWA_Reporting database with the logged on users credentials, the logged on user must be a part of the Reporting Authors AD group otherwise they will just see a SQL Server login box in Excel unless they have access directly to the SQL server / database.


When editing or creating Excel reports that use the OLAP Cube data, the logged on users credentials are used to authenticate at the data source. The access to the OLAP Cube data is controlled differently to the PWA_Reporting database. To access the OLAP Cube data the logged on user needs to be a member of the ProjectServerViewOlapDataRole against the Cube as shown below:


All users that are members of a Project Server security group that allows “View OLAP Data” will be added to the ProjectServerViewOlapDataRole role when the Project Server cube is built. Without the “View OLAP Data” permission allowed, the user will not be able to create or edit OLAP reports in Excel. If the user doesn’t have permission to the cube they will see this login box:


Authentication In Excel Services

Viewing Reports in Excel Services will authenticate users using the ProjectServerApplication Secure Store Service App. This is the same for both data sources, when the ProjectServerApplication target application is created there is a members field, this is where you allow users access. Microsoft recommends to create another Active Directory Security group for Report Viewers, then add this group in the members field as shown below:


All users who need to view the reports need to be added to the Report Viewers AD group. Once you have created the ProjectServerApplication target app, you then need to set the credentials of the user account that will be used to authenticate against each data source, this is shown below:


This account will need data reader access to the PWA_Reporting database and at least the ProjectServerViewOlapDataRole against the OLAP cube. When users refresh the Excel Services reports in the web, firstly it will check that they are a member of the ProjectServerApplication target app then the data source will be accessed using the credentials set for the ProjectServerApplication target app. If the account used for the ProjectServerApplication doesn’t have access to the data sources you will see the errors below:

Excel Services Error:

An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:


The same error is seen for each data source apart from the connection name.

ULS log error:

ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass1.<CreateConnection>b__0()     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)     at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated.

Error relinking Project Site to Project in #ProjectServer 2010 #PS2010 #Project #EPM

July 20, 2011 at 3:55 pm | Posted in Administration, Workarounds | 2 Comments
Tags: , ,

Do you receive the error below when trying to relink a Project Site to Project in PWA > Server Settings > Project Sites?

The Web Site does not exist or is not configured for Project Server. Enter a Web Site that has been extended with a Project Server compatible template.


You will normally run into the issue if you have deleted a Project from Project Server, not deleted the Project Site, create a new project plan and then want to relink the original Project Site. If your Project Site is based on a Project Server template then the usual cause for this error is that either you have %20 (URL encoding for a space) in the Site URL or a default list such as the Risks or Issues list has been deleted and maybe a new custom SharePoint list with the same name has been created instead.

Firstly instead of using the %20 for the space actually put a space in the address box, for example use PWA/PM 1 instead of PWA/PM%201. If that doesn’t resolve your issue it is usually caused by the default risks or issues list being deleted. When you relink a Project Site using the Edit Site Address button in PWA > Server Settings > Project Sites it will validate the site by checking if the default Project Server Risks and Issues lists are present on the site.

There are two methods to relink this Project Site back to the Project if you have deleted the default list or lists.

The best option would be to recreate the default lists as described in this article: This would mean having to recreate all of the list items again, but would mean that you don’t have any Reporting Project synch WSS errors in the Project Server queue when you publish the associated project.

The other option is to relink the Project Site as it is using the steps below. In this example the Project is called PM 1 with a URL of PWA/PM 1.

  • Navigate to the PM 1 site and click Site Actions > Site Settings > Title, description and icon and edit the URL to PM 1z and click OK.


  • Create a new blank site for PM 1 in PWA > Server Settings > Project Sites with a URL of PM 1


  • Navigate to this new blank PM 1 site and click Site Actions > Site Settings > Delete this site then click Delete (confirm it is the blank site before deleting!)


  • Navigate to the PM 1z site, click Site Actions > Site Settings > Title, description and icon and edit the URL to PM 1 and click OK.


  • This original Project Site is now linked back to the new Project

Hope that helps anyone facing this issue.

How to check the #Project Professional 2010 service pack / patch level #ProjectServer #PS2010

July 18, 2011 at 1:33 pm | Posted in Administration, Installation | Comments Off on How to check the #Project Professional 2010 service pack / patch level #ProjectServer #PS2010
Tags: , ,

Following on from a previous article relating to checking the farm patch level, I have quickly documented how to check Project Professional 2010 patch level below. The previous article can be found here:

To check the patch level for Project Professional 2010, launch Project Professional 2010. Click File > Help > Additional Version and Copyright Information


A dialogue box will appear like the one shown below:


The version number can be seen on the first line.

It is best practice that the patch level of Project Professional 2010 matches the the patch level of Project Server 2010.

#ProjectServer 2010 PSI data to a text file using #PowerShell #PS2010 #Project

July 15, 2011 at 3:30 pm | Posted in Functionality, PSI | 3 Comments
Tags: ,

I recently looked into accessing the Project Server PSI via PowerShell, luckily I came across this MSDN article that helped: I needed to get the data into a pipe delimited text file without using compiled code (I’m not a developer!). I have done this before with T-SQL and SSIS but I wanted to try something different. For the purpose of this blog article of have edited the example shown in the MSDN article. The PowerShell script is below:

$pwaUrl = "http://vm353/pwa"

$svcProjectUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl"

$c = Get-Credential

$svcProjectProxy = New-WebServiceProxy -uri $svcProjectUrl -credential $c

$svcProjectProxy.ReadProjectList().Project | Select Proj_name, Proj_UID, Proj_Type `
    | Export-CSV C:\projects.txt -Delimiter "|"

This script will export out a list of Project names, Proj UIDs and the Project Types to the C drive in a text file called projects.txt using the pipe delimiter. Screen shots below show the script and the output.





This is a very simple example but provides a no code solution to access Project Server data via the PSI.

#ProjectServer 2010 Reporting sync job failed – Foreign Key constraint #PS2010 #Project

July 15, 2011 at 12:18 pm | Posted in Fixes, Issue | 1 Comment
Tags: ,

Recently one of my colleagues came across this reporting synch job error with Project Server 2010 SP1 + June 2011 CU. In the blog post below the full error is listed along with the resolution for this particular issue and the details on how Frank got to the bottom of the error. I thought I would share this with you in case anyone comes across this in the future.


We received the following error message in the Queue when trying to publish a plan:

ReportingProjectChangeMessageFailed (24006) – The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "TEST_Project_Server_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.. Details: id=’24006′ name=’ReportingProjectChangeMessageFailed’ uid=’b15f1280-4555-47ca-a7e4-6e3b815183c7′ QueueMessageBody=’Project UID=’ff3a3de8-7af3-4b61-87ed-7424fce5569a’. PublishType=’ProjectPublish” Error=’The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MSP_EpmAssignmentByDay_ProjectUID_AssignmentUID". The conflict occurred in database "TEST_Project_Server_Reporting", table "dbo.MSP_EpmAssignment". The statement has been terminated.’.

This indicated to us that there is at least one assignment corrupted in the plan.

By running a SQL trace we could see that Project Server tried to write a time phased entry for an assignment into the MSP_EpmAssignmentByDay table. Examining this further we identified that for the record it tried to write no assignment record had been created in the MSP_EPMAssignment table in the reporting database. Based on the table constraints on the MSP_EpmAssignmentByDay table this was bound to fail.

Hypothesis: Project Server is not checking sufficiently before inserting into the MSP_EpmAssignmentByDay table if the assignment for the time phased entries actually exists in the MSP_EpmAssignment table (reporting database).

It appears that the publishing process is ignoring the assignment record that is present in the MSP_ASSIGNMENT table in the Published database. The reason why it is ignoring the records is unclear to us at this stage.

In order to resolve it we identified the task within the plan that had the issue using SQL Server Profiler. This is a very time consuming process and as such not a viable solution long-term.

Eventually we were able to identify the assignment row within the plan that was causing the error.

Since in this case only local resources were present in the plan (but the same steps should also work with enterprise resources) we followed the following approach to resolve the issue:

1.) Ensure the plan is archived (EPM backup)

2.) Create a new local dummy resource with the same Enterprise Calendar as the resource associated with the assignment/task in question

3.) Substitute the resource on the activity in question with the dummy resource (using the ‘Assign’ functionality in project pro).
Please note: This should usually not affect the duration / work value on a task unless there are resource calendar exceptions defined against the resource to be replaced which are not present for the dummy resource.
Furthermore this process would potentially cause issues if the task was partially or fully progressed.

4.) Save & publish the plan (the schedule did not appear to be affected by this change)
The publish job for reporting when through successfully this time

5.) Substitute the resource back with the original resource and delete the dummy resource from the resource sheet

6.) Re-publish the plan [publishing should work correctly – if not repeat the process and identify if there are more than one assignment that is corrupted in the plan]

The result is that the plan publishes now again. Our concern is that this is likely to be only a temporary measure and the issue will re-occur in due time.

Many thanks to my colleague Frank Merla for sharing this.

Manually adding users to Project Workspace / Project Site in #ProjectServer #PS2010 #PS2007 #EPM

July 11, 2011 at 11:41 am | Posted in Administration, Functionality | 9 Comments
Tags: , , ,

This post will detail the errors seen when trying to add or edit items in a Project Site / Project Workspace in Project Server when adding users manually to a Project Site when they don’t have the correct permission in Project Server. For the purpose of this guide I will base this on Project Server 2010 but the same applies for Project Server 2007.

A bit on the background information to start with so that it makes it clear later on in the post why you see these errors. Project Server has its own permission model that works differently to the SharePoint permission model. Users are added to Project Server via the PWA > Server Settings > Manage Users interface and not the SharePoint Site Actions > Site Permissions interface. This is because users will be added to the PWA site based on their Project Server permissions, to an extent the same can be said for the Project Sites. Although Project Sites appear and function the same as a normal SharePoint site, certain lists / document libraries work very differently to standard SharePoint lists / libraries. The Project Documents library, Risks list and Issues list link back to Project Server so therefore when editing / adding items, firstly the SharePoint permissions will be checked, then the Project Server permissions will be checked. If the user doesn’t have the correct permissions in Project Server, then they will see errors, even if they have full control on the Project Site. Each error is shown below.

In this example I have added a user directly to the Project Site, the user has Full Control of this Project Site, also this user does not have any access to PWA.

Project Documents:

When adding a document to the Project Documents library the user will see this error pop up once they click OK to add the document:


When they click, “Go back to Site” they will see that document has still been uploaded:


The error is thrown when the Project Server permissions are validated. The error below can be seen in the SharePoint ULS logs:

System.Web.Services.Protocols.SoapException: ProjectServerError(s) LastError=GeneralSecurityAccessDenied Instructions: Pass this into PSClientError constructore to access all error information    at Microsoft.Office.Project.Server.WebServiceProxy.Security.CheckUserProjectPermission(Guid projectUid, Guid categoryPermissionUid)     at Microsoft.Office.Project.PWA.CustomFieldWebControls.CustomPWALinkField.OnLoad(EventArgs e) 

As you can see from the error above, it fails when checking the Custom PWA Link field as this is used to link the Document back to a task in the Project. As this user doesn’t have access to Project Server the error is thrown.

Risks and Issues lists:

Users will not be able to create an item on the risks or issues list if they do not have the correct permissions in Project Server, they will see the error below when clicking New Item:


The error below can be seen in the SharePoint ULS logs:

System.Web.Services.Protocols.SoapException: ProjectServerError(s) LastError=GeneralSecurityAccessDenied Instructions: Pass this into PSClientError constructore to access all error information    at Microsoft.Office.Project.Server.WebServiceProxy.Security.CheckUserProjectPermission(Guid projectUid, Guid categoryPermissionUid)     at Microsoft.Office.Project.PWA.CustomFieldWebControls.CustomPWALinkField.OnLoad(EventArgs e)    

As a minimum, users will need permission in Project Server to use these lists, I would also recommend that you let Project Server control the users permissions to the Project Sites. To do this check the box “…automatically synchronize Project Web App users with Project Sites when they are created…”, on the PWA > Server Settings > Project Site Provisioning Settings page.


This will synch the users to the Project Site when the Project is published, it will add users to the Project Sites based on their Project Server permissions and therefore less of an administration overhead. The permission the users get on the Project Site can be seen below:

  • Project managers who have published a project or who have Save Project permissions on a project are added to the Project Managers (Microsoft Project Server) site group.
  • Team members with assignments in a project are added to the Team members (Microsoft Project Server) site group.
  • Other Project Server users who have View Project Site permission on a project are added to the Readers (Microsoft Project Server) site group.

Hopefully that explains why you see these errors on the Project Sites.

How to check the SharePoint 2010 and Project Server 2010 service pack / patch level #PS2010 #ProjectServer #SharePoint #SP2010

July 8, 2011 at 7:59 pm | Posted in Administration | 1 Comment
Tags: , , ,

This article will show how to check the farm version for SharePoint Foundation 2010, SharePoint Server 2010 and Project Server 2010.

The farm patch level can be check in the Central Administration site, click “Upgrade and Migration” then “Check Product and Patch Installation Status”


The patch level of all farm components will be detailed here, as you can see below, Project Server is listed at the bottom:


The farm configuration database version can also be checked on the “Manage Servers in this farm” page:


You can also check the SQL databases directly just running the query below:

select     version
        ,    ID
        ,    UserName
        ,    TimeStamp
from versions

Or if you want to see everything:

Select * from versions

This query can be executed against most of the SharePoint 2010 databases and all of the Project Server 2010 PWA databases. Worth noting is that not all SharePoint databases have a versions table, also not all of the versions tables get updated with every update, it usually depends if there is a schema update.

SQL Alias vs DNS Alias for Project Server 2010 install #PS2010 #EPM #ProjectServer

July 6, 2011 at 5:44 pm | Posted in Installation | 2 Comments
Tags: , ,

When installing Project Server 2010 it is always best practice to use an alias name for the SQL server as this makes life easier should you want to move SQL servers at a later date. This raises the question, SQL alias (using SQL cliconfg utility – pic below) or a network DNS Alias name (CNAME or HOST Record).


Pic1: SQL Server Client Network Utility (cliconfg)

For Project Server 2007 I would have said there weren’t really any advantages for one method over the other but for Project Server 2010 it’s different. For Project Server 2010 I would always recommend using a network DNS Alias, the reason being is to do with creating Excel Services reports.

When a user either creates a new or edits an existing report in Excel, their Excel client will connect directly to the data source (either Reporting database, custom database or OLAP cube). The ODC file will contain the connection details, SQL server name (alias name if Project Server was installed with an Alias for SQL), and database or cube name. If you installed Project Server 2010 with a SQL Alias on the Application Server, the user’s client machine will not know anything about the SQL Alias name unless you set up the same SQL Alias on all users machines who will create Excel Reports. This is because SQL Alias are only local to the machine that is it created on. Using a network DNS Alias to alias the SQL server will remove this issue.

Paul Mather

Next Page »

Blog at
Entries and comments feeds.