#ProjectServer Active Issues and Risks on PWA Reminders Web Part #PS2010 #SP2010 #SharePoint

July 13, 2012 at 11:05 am | Posted in Administration, Configuration, Customisation, Functionality, Issue, T-SQL | 1 Comment
Tags: , , , , ,

The Project Server reminders web part contains key information relating to all projects that the logged on user is involved in. This includes new task assignments, timesheet information, pending approvals and active issues and risks assigned to the logged on user. This post focuses on the issues and risks count as I have seen a number of queries relating to the issues and risks not showing on the reminders web part.

Firstly I will detail where this web part gets the data from and then show working / non working examples. This web part reads data from the Project Server Reporting database, two stored procedures create the dataset for the web part then the code on the web part counts how many of those issues and risks are active. The status value is key to the issues and risks count on the reminders web part, this will be shown in the examples later on. In the image below you can see that I have 3 active risks and 2 active issues assigned to me on my test system:


Lets now take a look what the two stored procedures return from the Reporting database. Firstly I need to get my ResourceUID to pass into the stored procedures using the simple query below against the Reporting database:


The two stored procedures are MSP_WSS_ReadResourceIssues and MSP_WSS_ReadResourceRisks. These two stored procedures require a RES_UID value, in this example I will use my ResourceUID C5733575-2C0A-4B40-A367-E33B82A1CB33 as shown below:


As you can see I have 3 risks and 4 issues assigned to me. Notice on the PWA reminders web part it shows 3 active risks which matches the SQL dataset and 2 active issues which doesn’t match the SQL dataset. The issues SQL dataset returns four issues, 3 of which are active in the lists and one which is postponed.

For active issues and risks to appear on the reminders web part it is key that the Status value is (1) Active, notice that the 4th issue has a value of Active without the (1).

If I change the issue list settings for the PM Testing Assn project site so that the Status choice value includes (1) Active then edit the item, the reminders web part will correctly show 3 active issues.

The current Status choice values on the PM Testing Assn project site are shown below:


Once this choice list has been updated to include the correct Active value and the issues edited the SQL dataset will contain the correct values and therefore so will the reminders web part. The choice values updated to the correct values shown below:


Now after editing the issue item the correct active Status value will appear:


The SQL stored procedures return the correct values after the Reporting (Project Sync) job has successfully completed in the Project Server queue:


Refreshing the PWA homepage now also shows the correct count on active issues and risks:


So the keys things to remember to make sure the reminders web part shows the correct counts for the active issues and risks are:

  • ensure the Issues and Risks list default status values are not updated
  • ensure the Reporting (Project Sync) jobs completed successfully

When updating the project site template it might be worth testing all default functionality, a useful project site smoke test can be found here:


#ProjectServer data snapshot for reporting #PS2010 #MSProject #SP2010 #Excel #SSRS #SQL

April 27, 2012 at 4:33 pm | Posted in Administration, Configuration, Customisation, Functionality, Reporting, T-SQL | Leave a comment
Tags: , , , , , ,

Quite often there is a requirement for some form of data snapshot in Project Server for trend analysis reporting. This post will give a simple explanation of how this can be achieved using the Project Server Reporting database and a SQL job. This example just captures some key project metrics such as Actual Work, Actual Cost, Status Date and a couple of custom field values each month to demonstrate the process.

Below is the first SQL query that will need to be run against the Project Server Reporting database to create the first snapshot:

select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
into        CustomSnapshotProjectData
from        dbo.MSP_EpmProject_UserView

The query above will get the data for the fields listed in the select statement from the MSP_EpmProject_UserView and insert the data into the CustomSnapshotProjectData table. Update the query to get the fields / data that you require to be snapshotted.

To add the next months data the SQL query will look slightly different as the CustomSnapshotProjectData table already exists. This time we need to insert into rather than select into, the query can be seen below:

Insert into CustomSnapshotProjectData (ProjectName, ProjectUID, ProjectType, ProjectActualWork, ProjectActualCost,ProjectStatusDate,[PM TEST], [PM TEST 2],SnapshotDate)
select        ProjectName
        ,    ProjectUID
        ,    ProjectType
        ,    ProjectActualWork
        ,    ProjectActualCost
        ,    ProjectStatusDate
        ,    [PM TEST]
        ,    [PM TEST 2]
        ,    DateName(month,GETDATE()) + ‘ ‘ + DateName(year,GETDATE())  as SnapshotDate
from        dbo.MSP_EpmProject_UserView

This can either be run manually each month at the specified time or a SQL job can be created to execute the SQL query automatically each month.

The contents of the CustomSnapshotProjectData table can be seen below, please note the data in my test system is not ideal for reporting / snapshotting:


This data can be accessed using the usual reporting tools such as Reporting Services and  Excel / Excel Services.

As mentioned this is just a simple example to show how quickly and easily snapshotting can be set up for Project Server 2007 and 2010.

#ProjectServer 2013 / 2010 / 2007 high-level Audit Export via #PowerShell #MSProject #PS2010 #EPM

March 5, 2012 at 6:15 pm | Posted in Add-on, Administration, PowerShell, PSI, Reporting | 1 Comment
Tags: , , , , , , ,

Quite often there is a request for audit information from Project Server but unfortunately there is nothing available out of the box. This post covers a very high-level solution to this using the Project Server PSI and PowerShell.

Most actions in Project Server are processed via the Project Server queue, one simple way to get high-level audit information is to extract the queue information. The following PowerShell script uses the ReadAllJobStatusSimple method from the Queue System web service to export yesterdays processed jobs into a txt file with yesterday date appended to the filename:

The script can be downloaded from the script center:


$Today = Get-Date
$Yesterday = $Today.AddDays(-1).ToString(“yyyy-MM-d”)
$Filename = “C:\PSAuditExport\QueueExport-”
$filetype = “.txt”
$svcPSProxy = New-WebServiceProxy -uri “http://vm353/pwa/_vti_bin/PSI/QueueSystem.asmx?wsdl” -useDefaultCredential
$svcPSProxy.ReadAllJobStatusSimple(“$Yesterday 00:00:01”, “$Yesterday 23:59:59”, “200”, “0”, “QueueCompletedTime” ,”Ascending”).Status | Export-CSV $Filename$Yesterday$filetype -Delimiter “|”

Update the filename variable and PWA URL, save the script and execute using a batch file that is scheduled via the Windows task scheduler sometime after midnight to get yesterdays jobs. An example command needed in the batch file is below:

“%SYSTEMROOT%\system32\windowspowershell\v1.0\powershell.exe” -command “& ‘C:\QueuejobstatusAudit.ps1′”

Once executed this will export all of the jobs for that day to a text file in the specified location, in this case C:\PSAuditExport as shown below:


An example export looks like this:


The export could then be used for auditing purposes. For example, to see who saved or published a particular project you could search for the Project GUID in the export file and find the GUID’s of the Resources / users that saved or published that project on that particular day.

This is a very simple solution that I put together for the purpose of this post, this could be taken a lot further and made into a production solution.

#ProjectServer 2010 #MSProject Enterprise Global custom fields out of synch #PS2010 #PS2007

February 22, 2012 at 7:43 pm | Posted in Administration, Configuration, Issue | 5 Comments
Tags: , , , , ,

I recently came across an issue where the enterprise custom fields in the Enterprise Global didn’t match what was displayed in PWA > Server Settings > Enterprise Custom Fields and Lookup tables. I am not sure how or why this has happened, all I know it is seemed to be a combination of an upgrade from Project Server 2003 to Project Server 2010 with a playbooks merge of two Project Server 2007 instances(the two 2003 environments upgraded) along the way. It was decided to remove some of the Project Level custom fields from the Project Server 2010 configuration as they were no longer required. After successfully deleting the customs fields from Server Settings > Enterprise Custom Fields and Lookup tables, Project Professional 2010 was launched, connected to the server instance and found that the deleted custom fields were still present in the Project Information dialog box. I tested deleting the local cache, the local global template but still these deleted fields appeared in the Project Information dialog box. This issue was replicated from all Project Professional 2010 clients. At this point I decided to recreate the Enterprise Global using the steps below:

  • Take a full backup of the Project Server databases / farm
  • Backup the Enterprise Calendars from the Admin backup menu
  • Copy all of the enterprise global elements to a project using the organiser, these include views, tables, filters, groups, reports, maps, forms and toolbars. Save this project as a local MPP file.
  • Run the SQL script that creates the enterprise global (eglobal.sql) against the ProjectServer_Published database. The default location for the script is:

    C:\Program Files\Microsoft Office Servers\14.0\Sql\Project Server\CORE\1033

  • Once the enterprise global has been rebuilt successfully the Project Information dialog box displayed the same fields as shown in Server Settings > Enterprise Custom Fields and Lookup tables so the deleted fields had now been removed from the enterprise global. Now the enterprise global custom elements need to be copied back in. Open and check out the enterprise global, open the MPP file that was used to save all of the elements and then use the organiser to move all of the custom elements back into the new enterprise global.
  • Restore the Enterprise Calendars

These steps resolved the issues on the environment I was working on but please proceed with caution and make sure you have full backups in case you need to roll back.

Check for missing config after #ProjectServer 2007/2010 PWA instance merge #PS2010 #PS2007 #MSProject

February 20, 2012 at 10:22 pm | Posted in Administration, Configuration, Functionality, Migration, T-SQL | Leave a comment
Tags: , , , , ,

I recently worked on a Project to migrate two Project Server 2003 instances into one Project Server 2010 instance. We achieved this by migrating both Project Server 2003 instances to two separate Project Server 2007 instances then merged the smaller of the two instances into the other instance in 2007. Luckily we only needed to migrate the configuration from 2003 in 2010. For this we took a playbooks export of the global configuration from the smaller instance and imported this into the other PWA instance using the merge option. On the whole this approach worked quite well, with only a few errors on importing a couple of custom fields and views. There were some items that we needed to migrate manually (Resource Pool, Enterprise Global etc.).

Now that we had the two environments merged into one 2007 PWA instance we wanted to confirm that we had all of the entities from both systems in the one PWA instance that we were going to take forward to Project Server 2010. To do this I used a simple SQL query that runs against the Published database. Please note, it is not officially supported to query the published database directly with T-SQL so please do so at your own risk. I have used the no lock hint to avoid any tables locks and this was not run on a live production environment. The query used is below:

–SOURCEDB_Published is the PWA instance where the export was taken from

–TARGETDB_Published is the PWA instance where the exported config was imported and merged

–custom fields

use SOURCEDB_Published


from dbo.MSP_CUSTOM_FIELDS CF with (nolock)

where CF.MD_Prop_Name not in (select CFT.MD_PROP_NAME from TARGETDB_Published.dbo.MSP_CUSTOM_FIELDS CFT with (nolock))

–lookup tables

use SOURCEDB_Published

select LT.LT_NAME

from dbo.MSP_LOOKUP_TABLES LT with (nolock)

where LT.LT_NAME not in (select LTT.LT_NAME from TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock))

–lookup table values (text values)

use SOURCEDB_Published

select LV.LT_VALUE_FULL, LT.LT_name AS [Lookup Table Name]

from dbo.MSP_LOOKUP_TABLE_VALUES LV with (nolock)

Inner Join dbo.MSP_LOOKUP_TABLES LT with (nolock)


where LV.LT_VALUE_TEXT not in

(select Cast(LVT.LT_VALUE_FULL as nvarchar(max))

from TARGETDB_Published.dbo.MSP_LOOKUP_TABLE_VALUES LVT with (nolock)

Inner Join TARGETDB_Published.dbo.MSP_LOOKUP_TABLES LTT with (nolock)



use SOURCEDB_Published

select C.CAL_NAME

from dbo.MSP_CALENDARS C with (nolock)

where C.CAL_NAME not in (Select CT.CAL_NAME from TARGETDB_Published.dbo.MSP_CALENDARS CT with (nolock) where CT.Cal_is_base_Cal = 1)

–views (PWA)

use SOURCEDB_Published


from dbo.MSP_WEB_VIEW_REPORTS V with (nolock)

where V.WVIEW_NAME not in (Select VT.WVIEW_NAME from TARGETDB_Published.dbo.MSP_WEB_VIEW_REPORTS VT with (nolock))

–security categories

use SOURCEDB_Published


from dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock)

where CAT.WSEC_CAT_NAME not in (Select CAT.WSEC_CAT_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_CATEGORIES CAT with (nolock))

–security groups

use SOURCEDB_Published


from dbo.MSP_WEB_SECURITY_GROUPS G with (nolock)

where G.WSEC_GRP_NAME not in (Select GT.WSEC_GRP_NAME from TARGETDB_Published.dbo.MSP_WEB_SECURITY_GROUPS GT with (nolock))

Hopefully that will help you validate certain entities in the PWA instance when merging two instances into one.

Project Server 2003 migration to #ProjectServer 2007 error #PS2007 #PS2010 #MSProject

February 13, 2012 at 6:23 pm | Posted in Issue | Leave a comment
Tags: , , , , , ,

I recently worked on a project to migrate from Project Server 2003 to Project Server 2010. While running the P12 migration tool the error below was thrown in the command prompt window:

PWA Migration: Setup views to Project Server 2003 – Failed

The Log file details for the error were as follows:

Err1 = 37000 Err2 = [Microsoft][ODBC SQL Server Drive][SQL Server] could not find server ‘server name’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the sp_addlinkedserver to add the server.

Migration finished with error return code 80004005.

Running the –verify switch completed successfully but running the actual migration failed. 

This is due to using an FQDN or DNS Alias name for the SQL Server in the P12 configuration file rather than the actual short name for the SQL Server. After updating the configuration file “Project2007SQLServer” value the migration ran successfully.  

#ProjectServer 2010 Quick Launch configuration page issue after upgrade from #PS2007 #PS2010 #MSProject #SP2010

November 28, 2011 at 2:27 pm | Posted in Administration, Fixes, Issue | Leave a comment
Tags: , , , , ,

I recently investigated an issue where the Quick Launch configuration page in PWA > Server Settings didn’t display the links / sections correctly in Project Server 2010. The sections would not expand so therefore you could not change any settings for these links. The issue appears to exist only when upgrading from Project Server 2007 when the 2007 PWA site has custom links on the quick launch.

Below you can see how the Quick Launch configuration page looked in the 2007 environment:


Notice the custom SSRS link below Server Settings.

And how this page looks after upgrading to Project Server 2010:


After upgrading it is not possible to expand all sections / headings apart from the section that is at the top. In the screen shot above you can see that the Projects section is expanded, in the screen shot below you can see that after moving My Work to the top, this is now expanded but the Projects section is now collapsed.


The only fix for this that I know of is to remove the custom link from the MSP_SiteMap table in the Published database. Direct access to the Published database is not recommended or officially supported by Microsoft. If you do come across this issue I would recommend that you open a support case with Microsoft but if you want to proceed at your own risk please see the steps below. I would carry this out on a replica Test environment first before applying to any Production environment, also make sure you have full database backups.

You might need to get the SQL DBA to carry out the SQL tasks due to SQL Server access. Launch SQL Server Management Studio and run the following query against the Published database to get the SM_UID value for your custom link:

Select        SM_UID       
        ,    SM_CUSTOM_URL
        ,    SM_CUSTOM_TITLE
From        dbo.MSP_SITEMAP
where        SM_CUSTOM_TITLE = ‘SSRS’ — update the title for your custom link title


As you can see, for my custom link which was called SSRS in my PS 2007 environment the SM_UID value is B1EA03CC-7584-4F6B-9DBD-0655B6970471. Once you have the SM_UID value for your custom link you can delete the custom link using the MSP_SITEMAP_DeleteMenuItem Stored Procedure on the Published database:

Right click on the MSP_SITEMAP_DeleteMenuItem stored procedure and click “Execute Stored Procedure” as shown below:


Enter the SM_UID value surrounded by singe quotes in the Value column, so in this example I entered ‘B1EA03CC-7584-4F6B-9DBD-0655B6970471’


Click OK to execute the stored procedure and you should see the following appear:


You will need to repeat this for all custom links.

Now refresh the Quick Launch configuration page and you will see that all menu sections / headings are now expanded correctly:


You can now re add any custom links that were present in the PS 2007 environment into the PS 2010 environment:


As mentioned above, if you do find yourself with this issue I would recommended opening a support case with Microsoft but if you wish to proceed at your own risk please do so on a replica test environment before carrying this out on a Production environment.

Investigating #ProjectServer and #SharePoint errors #PS2007 #SP2007 #MOSS #PS2010 #SP2010

November 12, 2011 at 9:49 am | Posted in Administration, Information | 1 Comment
Tags: , , , , , , , , ,

This is quick a quick post to summarise the main key areas that can be used when investigating any Project Server issues, I have included SharePoint as most would also apply to SharePoint.

Firstly I will just list the key tools / logs that can be used then go on talk about each one in more detail if needed.

  • Project Server Queue
  • SharePoint ULS logs / Monitoring
  • IIS logs
  • Windows Event logs
  • Web.Config (custom errors off)
  • SQL Server logs
  • SQL Server Profiler

Project Server Queue

It is good practice to monitor the Project Server queue daily to make sure that jobs are processing and to catch any errors as soon as possible, as you can see below there are no jobs failed or processing currently in my test environment Smile


If you find that a user complains about a issue that occurred the previous day, change the Job history back a day and refresh the status to see if anything failed in the queue.


If you come across any failed jobs click the message in the Error field “Click to view the error details”


A new window will pop up giving you more details on the error:


In this case I entered the incorrect SMTP server so that I could generate an error for the purpose of this post! If you scroll down the error message but don’t find anything helpful, at the end you will see a JobUID to search the SharePoint ULS logs for more details.


Copy the JobUID then take a look at the SharePoint ULS logs, which leads us on the the next area.

SharePoint Unified Logging Service (ULS) / Monitoring

I wont go into details as there is a very good MSDN article found here for 2010: http://msdn.microsoft.com/en-us/library/gg193966.aspx. The ULS log viewer is recommended, for more details:http://blogs.msdn.com/b/opal/archive/2009/12/22/uls-viewer-for-sharepoint-2010-troubleshooting.aspx

Project Server 2007 / SharePoint 2007 also uses the SharePoint ULS logs, the default locations is of the log files on the SharePoint server(s) is C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS.

Following on from the Project Server queue error, you would open up the log file that is likely to contain more details (check the Project Server manage queue page for the time that the job failed) then search for the JobUID given in the error. In this example the JobUID is 8fe02d26-8892-4144-9d27-b07f21cb3f54


Examine the logs and hopefully you should be able to workout why this failed or once you have what looks to be a meaningful error copy and paste this into your favourite Internet search engine and you might find a fix!

Also worth noting is the System Centre Operations Manager (SCOM) management pack for monitoring SharePoint / Project Server 2010: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4419 and for Project Server 2007: http://www.microsoft.com/download/en/details.aspx?id=24107

Knowledge articles for SCOM and Project Server 2010: http://technet.microsoft.com/en-us/library/ff678247.aspx

IIS logs

IIS might be usefully when diagnosing page load issues. The logs can be found in the following locations by default:

IIS 6.0 – %windir%\System32\LogFiles

IIS 7.0 / 7.5 – %SystemDrive%\inetpub\logs\LogFiles


Updating the web.config file(s) is useful to see actual pages errors rather than seeing the custom friendly error if an error occurs. There are plenty of articles on the Internet about turning of SharePoint custom errors in the web.config files.

SQL Server logs

The SQL server logs can be useful when investigating issues depending on what the issue is, looking at the SQL logs you would quickly be able to identify any failed database login attempts, database locks, if a database file is full etc.


SQL Server Profiler

The SQL server profiler is a very useful tool for investigating Project Server issues, for example is you have a Project that continuously errors on save or publish you can run a SQL trace while you submit another save or publish job then look though the output and hopefully see where the issue occurs. It would help you get to the bottom of the issue, whether it is a resource issue, task or assignment issue etc. Worth noting here is, please do not make any direct database updates, make changes via Project Professional / PWA / custom code via the PSI.

For more details on the SQL Server Profiler see the following MSDN article: http://msdn.microsoft.com/en-us/library/ms187929.aspx

If you find yourself with an issue that you can’t get to the bottom of I would recommend starting a new post on the Project Server forum found here: http://social.technet.microsoft.com/Forums/en-US/category/projectserver2010

#ProjectServer 2007 #MOSS #Office 2007 #MSProject SP3

October 25, 2011 at 10:55 pm | Posted in Service Pack | Leave a comment
Tags: , , , ,

Service Pack 3 have now been release for Project 2007 and Project Server 2007 (along with the other Office 2007 products) – http://blogs.technet.com/b/office_sustained_engineering/archive/2011/10/25/office-2007-and-sharepoint-2007-service-pack-3-availability.aspx

Project 2007 SP3: http://support.microsoft.com/kb/2526091

Office Server 2007 SP3 (includes Project Server 2007): http://support.microsoft.com/kb/2526299

Windows SharePoint Services 3.0 SP3: http://support.microsoft.com/kb/2526305

As always, deploy to a Test / Dev system and fully test before deploying to any Production system.

Accessing custom fields that allow multiple values from look up tables in #ProjectServer via T-SQL #MSProject #PS2010 #PS2007

September 7, 2011 at 2:12 pm | Posted in Customisation, T-SQL | 3 Comments
Tags: , , , , ,

At recent query came up on the Project Server forum regarding accessing custom fields that allow multiple values in the PWA_Reporting database. This is quick post to show how this can be achieved. In this example I have a single value lookup custom field called ProjectStatus and a custom field that allows multiple values called MVLookUpField. The example SQL is below:

SELECT        proj.ProjectName
        ,    proj.ProjectStatus — example single value lookup field
        ,    lt.MemberFullValue AS N’MVLookupField’
FROM        dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN    [dbo].[MSPCFPRJ_MVLookupField_AssociationView] AS MVassoc — view for multi value field
            ON proj.ProjectUID = MVassoc.EntityUID
LEFT OUTER JOIN    dbo.MSP_EpmLookupTable AS lt
            ON MVassoc.LookupMemberUID = lt.MemberUID
order by    ProjectName asc

It is just simple select query but will hopefully it will give others an idea of how to get multi value custom fields from the PWA_Reporting database.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.