Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
(August 20, 2009 update, check out this Second Report Pack for Project Server 2007)
We have released last week an updated version of our EPM 2007 demo VPC. Please find below the SQL Server Reporting Services (SSRS) reports used in that VPC.
All these SSRS reports are using the Project Server 2007 Reporting database as a data source.
Please note the Earned Value Trend reports will require a custom stored procedure (included in the attachment).
This pack includes the following reports:
To deploy the Project List and Project Detail reports check the following post: Fields required to run Project List and Project Detail Reports; for the Timesheet Compliance report check this post:How to deploy the Timesheet Compliance Report
FYI I have just completed the install of Project Server 2007 + Service Pack 1 (and Office Server 2007 + SP1 as well) using Windows Server 2008 Release Candidate 1 (RC1) from MSDN.
I used this two great posts from Bill Baer to complete the install:
Two screen captures to prove it (check bottom right corner :)
Expect more posts in the future about the advantages of running PS 2007 on W2K8 (IIS7, hypervisor-based virtualization ...).
In the meantime for more information about Windows Server 2008, please check the official Microsoft site here: http://www.microsoft.com/windowsserver2008/default.mspx
(updated on 5/14, SP2 webcast recording and deck are here)
We are happy to announce that Service Pack 2 (SP2) for Office Project 2007 and Office Project Server 2007 has been released to the Microsoft Download Center. It includes all the fixes prior to SP2 (including the February 2009 Cumulative Update), and also several enhancements to Project Standard and Professional, Project Server, SharePoint Server 2007 and Windows SharePoint Services 3.0.
Before you install this service pack there are some very important things to understand. In this mail I'll try to provide you with the resources you need to be successful in your updates. It is essential that you understand the appropriate links, and thoroughly read the guidance and test out the patch in a separate environment prior to a production rollout.
I also encourage everyone to attend the Project 2007 Service Pack 2 Overview webcast scheduled for April 29 at 8 am Pacific Time, and April 30 at 5 pm Pacific Time (webcast details are posted on EPMConnect). Adrian Jenkins and myself will cover the following topics during the webcast: SP2 Overview, April Cumulative Update Overview, Deployment Best Practices, Next Steps, Questions and Answers.
Service Pack 2 (SP2) Description
Service Pack 2 (SP2) Download Center Page
WSS Language Pack (x86)
Service Pack 2 (SP2) Deployment Resources
Service Pack 2 (SP2) Related Technical Resources
Once your farm has been properly updated the new SP2 product version will be: 220.127.116.1122.1000 (database) and 18.104.22.16825.1000 (dll).
Getting Ready for Project Server 2010
As part of the SP2 release we announced the following requirements for the next release of SharePoint and Project Server: Windows Server 2008 and 64 bit. We will be publishing guidance on how to upgrade your existing Project Server 2007 farm to Windows Server 2008 64 bit in the coming weeks on TechNet. Expect full system requirements for Project Server 2010 at a latter date.
Please note we will release a Service Pack 2 for Project Portfolio Server 2007 within the next two months, I will send an email once it has been released.
[ Updated 4/25/2011 – modified code to support Windows 7, please not that this functionality is out of the box in Project Server 2010 and no longer requires such VBA script!]
A great question we heard this morning during the Project 2007 SP2 webcast and every time we release a server and desktop software update for Project 2007 and Project Server 2007, is how do I ensure the desktop contains the proper software updates? For instance how I do ensure that ALL desktop version for Project that connect to Project Server have Service Pack 2 installed?
In this first approach change MIN_PROJ_VERSION to the latest SP2 version number: 6425
Feel free to customize the Warning message at will!
Declare Function RegCloseKey& Lib "advapi32.dll" (ByVal hKey&)
Declare Function RegOpenKeyExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszSubKey$, ByVal dwOptions&, ByVal samDesired&, lpHKey&)
Declare Function RegQueryValueExA& Lib "advapi32.dll" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, ByVal lpDataBuff$, nSize&)
Declare Function RegQueryValueEx& Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey&, ByVal lpszValueName$, ByVal lpdwRes&, lpdwType&, lpDataBuff&, nSize&)
Const MIN_PROJ_VERSION = 6330
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const ERROR_SUCCESS = 0&
Const REG_SZ = 1& ' Unicode nul terminated string
Const REG_DWORD = 4& ' 32-bit number
Const KEY_QUERY_VALUE = &H1&
Const KEY_SET_VALUE = &H2&
Const KEY_CREATE_SUB_KEY = &H4&
Const KEY_ENUMERATE_SUB_KEYS = &H8&
Const KEY_NOTIFY = &H10&
Const KEY_CREATE_LINK = &H20&
Const READ_CONTROL = &H20000
Const WRITE_DAC = &H40000
Const WRITE_OWNER = &H80000
Const SYNCHRONIZE = &H100000
Const STANDARD_RIGHTS_REQUIRED = &HF0000
Const STANDARD_RIGHTS_READ = READ_CONTROL
Const STANDARD_RIGHTS_WRITE = READ_CONTROL
Const STANDARD_RIGHTS_EXECUTE = READ_CONTROL
Const KEY_READ = STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY
Const KEY_WRITE = STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or KEY_CREATE_SUB_KEY
Const KEY_EXECUTE = KEY_READ
Dim projVersion As String
Dim version As String
projVersion = RegGetValue$(HKEY_LOCAL_MACHINE, "Software\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\00002109B30000000000000000F01FEC\InstallProperties", "DisplayVersion")
version = Mid(projVersion, 6, 4)
If (CInt(version) < MIN_PROJ_VERSION) Then
MsgBox "Your version of Winproj.exe is not valid and may cause problems." & Chr(13) & Chr(13) & _
"The installation of new version of Microsoft Project is required!" & Chr(13) & Chr(13) & _
"Required Version: " & Left(projVersion, 5) & MIN_PROJ_VERSION & ".XXXX" & (Chr(13)) & _
"Found Version: " & projVersion & Chr(13) & Chr(13) & _
"Microsoft Project will now Exit." _
, vbExclamation, "Program Version Check"
Function RegGetValue$(MainKey&, SubKey$, value$)
' MainKey must be one of the Publicly declared HKEY constants.
Dim sKeyType& 'to return the key type. This function expects REG_SZ or REG_DWORD
Dim ret& 'returned by registry functions, should be 0&
Dim lpHKey& 'return handle to opened key
Dim lpcbData& 'length of data in returned string
Dim ReturnedString$ 'returned string value
Dim ReturnedLong& 'returned long value
If MainKey >= &H80000000 And MainKey <= &H80000006 Then
' Open key
ret = RegOpenKeyExA(MainKey, SubKey, 0&, KEY_READ, lpHKey)
If ret <> ERROR_SUCCESS Then
RegGetValue = ""
Exit Function 'No key open, so leave
' Set up buffer for data to be returned in.
' Adjust next value for larger buffers.
lpcbData = 255
ReturnedString = Space$(lpcbData)
' Read key
ret& = RegQueryValueExA(lpHKey, value, ByVal 0&, sKeyType, ReturnedString, lpcbData)
If ret <> ERROR_SUCCESS Then
RegGetValue = "" 'Value probably doesn't exist
If sKeyType = REG_DWORD Then
ret = RegQueryValueEx(lpHKey, value, ByVal 0&, sKeyType, ReturnedLong, 4)
If ret = ERROR_SUCCESS Then RegGetValue = CStr(ReturnedLong)
RegGetValue = Left$(ReturnedString, lpcbData - 1)
' Always close opened keys.
ret = RegCloseKey(lpHKey)
Another way to validate the desktop version, uses the file system. The only challenge with this approach is determining the location of the Program Files directory.
Dim MainFolderName As String
Dim LastMod As String
Dim Created As String
Dim Size As String
Dim projVersion As String
Set objShell = CreateObject("Shell.Application")
MainFolderName = "C:\Program Files\Microsoft Office\Office12"
Set FSO = CreateObject("scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(MainFolderName)
'error handling to stop the obscure error that occurs at time when retrieving DateLastAccessed
On Error Resume Next
For Each fil In oFolder.Files
Set objFolder = objShell.Namespace(oFolder.Path)
Set objFolderItem = objFolder.ParseName(fil.Name)
Select Case UCase(fil.Name)
Case Is = "WINPROJ.EXE"
LastMod = fil.DateLastModified
Created = fil.DateCreated
Size = fil.Size
MsgBox ("File: " + fil.Name + " Last Modified: " + LastMod + " Created: " + Created + " Size: " + Size)
'do all of the normal tests here to determine which version you need and which you have. See above.
On December 11th, 2007 Microsoft released Service Pack 1 (SP1) for the 2007 Microsoft Office System. SP1 addresses the issues of most concern to customers: stability, performance, and security. The service pack includes previously released updates and fixes.
Since Project Server, Project Professional and Project Portfolio Server are all part of the Office product family their respective SP1 was also released at that time.
Before you install the service pack there are some very important things to understand. In this mail I'll try to provide you with the resources you need to be successful in your updates. It is essential that you understand the appropriate links, and thoroughly read the guidance and test out the patch in a separate environment prior to a production rollout.
Overview and What's Included in SP1
For a description of SP1, please refer to the following knowledge base articles on http://support.microsoft.com/ (please note not all KB are online yet but should be shortly):
For a list of issues fixed in SP1, please refer to the following knowledge base articles on http://support.microsoft.com/ :
Critical Planning and Deployment & Installation Information
Next before installing read the following documents which will help you understand the deployment procedure as well as tips tricks and troubleshooting steps:
How to obtain and install SP1?
Patches for all products are available on Microsoft Update (recommended), Office Update and the Download Center with a few exceptions. Windows SharePoint Services and the WSS Language Packs are live on Windows Update and Microsoft Update. For those customers with low bandwidth, CDs will be available for order after January 15th for a small shipping and handling fee from a link off the Download Details page.
For additional information on each release, please see the download details page, in English, for:
Steps you will need to follow to display PWA Web Parts in another site within the same SharePoint farm:
1. Chose the Web Part (e.g. TimesheetPart) from the PWA homepage or the web parts gallery (Project Web Access > Site Settings > Galleries > Web Parts) and Export and Save to your preferred desktop location:
2. Edit the Web Part in Notepad. Find the following property PSIURL (or add if it does not exist) and change it accordingly for instance:
<property name="PsiUrl" type="string"> http://server/PWA</property>
3. Save Web Part update
4. From http://server/sites/project - Site Collection EPM > Web Part Gallery > Upload Document, select TimesheetPart modified earlier.
5. Add Web Part to the page you like (Site Action -> Edit Page)
To cross SharePoint server farms, you can use the Page Viewer web part in conjunction with SimpleUI=31
If you encounter the following while adding the Web Part to the page : “Unable to add selected web part(s). My Timesheet: The request failed with HTTP status 401: Unauthorized” check the ULS logs. It’s typically an access right issue so make sure the user has access to the PWA site.
Project Server 2007 Web Parts
Following this post (New SQL Reporting Services Sample Reports for Project Server), please find below the list of Excel Services (samples attached) reports included in the newly released demo VPC.
If you want to learn more about Excel Services (requires SharePoint Server) two new great books have been published on the subject:
In MS Project you have the ability to add notes to each tasks in a project plan. This data is stored as an Image Data Type in the SQL database called TASK_RTF_NOTES (note that the field TASK_NOTES is a nvarchar of size 255, thus you’ll only get the first 255 characters displayed). So how can you render the entire note field using SQL Server Reporting Services? Before giving you the answer a special thank you to Sam Brooks from Microsoft for passing along this solution:
(SQL Reporting Services code sample attached at the bottom of this post)
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
AND P.ProjectName='Task RTF' --- For debugging purposes REMOVE!!!!
ORDER BY P.ProjectName, T.TaskName
Note that the TASK_RTF_NOTES is not in the Project Server Reporting database, thus we need to get it from the Published database.
‘Instantiate a rich text box control in memory
Public rtfRTB As new System.Windows.Forms.RichTextBox
‘Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string
on error goto errortrap
s.length = 0
For i = 0 To b.Length - 1
If i <> b.Length - 1 Then
mystr = left(s.ToString, len(s.ToString)-1)
rtfRTB.rtf = mystr
s.length = 0
Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx
On July 15th, 2008 Microsoft announced the availability of the Infrastructure Update for Office Servers. The Infrastructure Update for Office Servers is a set of updates to improve platform performance and contain several customer driven fixes. The updates are applicable to Microsoft Office SharePoint Server 2007, Windows SharePoint Services 3.0, Microsoft Search Server 2008 & Microsoft Search Server 2008 Express, Microsoft Office Project Server 2007 and Microsoft Office Project 2007.
There are several noteworthy new features and fixes shipped in these updates for Project Server 2007 and Project 2007; specifically:
Please read Project 2007 Infrastructure Update Release for Server and Client to learn about its content in detail.
The Infrastructure Updates are available as free downloads to customers via the download center on http://www.microsoft.com/download.
Before you install the Infrastructure Update there are some very important things to understand. In this post we'll try to provide you with the resources you need to be successful in your updates. It is essential that you understand the appropriate links, and thoroughly read the guidance and test out the patch in a separate environment prior to a production rollout.
Full installation instructions and guidance is provided in the Knowledge Base articles linked from the download pages for each update along with existing TechNet guidance for patching Office Servers. The links are also included further on in this Q&A, but for reference, the following products require the following updates to be applied.
Read more about the new SharePoint features here
Read more about the new Search features here
Read more about the Content Deployment updates here
Knowledge Base Articles
It is strongly recommended that you install Windows SharePoint Services 3.0 Service Pack 1 and Office Servers Service Pack 1 before installing the Infrastructure Update for Microsoft Office Servers (KB951297) and the Infrastructure Update for Windows SharePoint Services 3.0 (KB951695).
The installation process will incur server and farm downtime that you will need to plan for – the updates should be installed on all servers in a farm.
Infrastructure Update for Microsoft Office Servers (KB951297)
Frequently Asked Questions
Q: Is Office Servers Service Pack 1 (SP1) a prerequisite or installed as part of this fix?A: No. Our supportability commitments to customers include providing the ability to install hotfixes on the two most recent versions of a product, in this case RTM and SP1. So installing these updates directly onto an RTM server is not blocked and will install some of the fixes shipped in Office Servers Service Pack 1, but only those that are contained in files that are changed by the Infrastructure updates.
Q: Can I uninstall the server updates?A: No. The Infrastructure Update for Microsoft Office Servers (KB951297) and the Infrastructure Update for Windows SharePoint Services 3.0 (KB951695) cannot be uninstalled. Both updates make database schema changes.
Q: Can I install the “Infrastructure Update for Microsoft Office Project 2007 (KB951547)” Project 2007 client update without installing the “Infrastructure Update for Microsoft Office Servers (KB951297)” on the server?A: Yes, the “Infrastructure Update for Microsoft Office Project 2007 (KB951547)” includes fixes for both client/server communication and local client features, so if you don’t have Office Project Server 2007 but use Office Project 2007 client this update can be safely installed and you will be able to take advantage of all the client updates.
Q: Where can I find information on Service Pack 1 for Project Server and Project Professional 2007?
A: Please check the following: http://blogs.msdn.com/chrisfie/archive/2007/12/12/announcing-the-release-of-epm-2007-service-pack-1.aspx
Q: What if I have an issue that isn’t addressed by this update?A: If your customer has a specific issue that these updates do not address you should follow the Microsoft Support process to log the issue and request a hotfix.
The Microsoft Office team has changed the way that it delivers hot fixes for reported problems by moving away from the current priority-driven hot fix release model to a scheduled delivery model. This change comes in the form of cumulative updates and critical on-demand (COD) hotfixes. The objective is to deliver high-quality fixes in an acceptable time and on a predictable schedule-every two months so its creates more predictability for customers.
Customers who need an emergency fix can request a shorter turnaround time for a COD hotfix.
Please refer to the following knowledge based article for more information: http://support.microsoft.com/kb/953878
The August Cumulative Update (CU) for Project 2007 and Project Server 2007 (as well as the rest of the Office suite) is the first CU release. Releases will now happen very two months so the next ones are scheduled for October 08, December 08, etc…
Install or not to Install?
Our general guideline for applying hotfix is:
Assuming you are running the released version of Project Server 2007, here is a high level procedure, please refer to the TechNet articles for detailed deployment steps:
1. Deploy Service Pack 1
Announcing the release of EPM 2007 Service Pack 1 (includes links to KB, downloads and deployment documentation)
Deploy Service Pack 1 for Office Project Server 2007
2. Deploy the Infrastructure Update (IU). We recommend deploying the IU first because you will get all the latest updates for WSS and SharePoint Server.
Announcing the availability of the Project and Project Server 2007 Infrastructure Update
Microsoft Project Infrastructure Update Webcast Slides
Deploy the Infrastructure Update for Office Project Server 2007
Install the Infrastructure Update for Microsoft Office Servers (Office SharePoint Server 2007)
3. Deploy the August Cumulative Update. Since the August CU are individually package (one EXE for Project Server and another one for SharePoint Server for instance), then you will have to deploy each one depending on the hotfix required.
4. Run hotfix EXE
5. Run PSCONFIG on each server within your farm to finalize the update
6. Verify Installation by verifying the version of each of the four PS database using the following command:
SELECT * FROM VERSIONS
Result should be: 12.0.6327.5000
See links below for KB and download location.
Ideally Project Professional 2007 and Project Server 2007 should be on the same version but it is not a requirement:
Knowledge Base Articles and Downloads
Cumulative update packages for August 2008 for the 2007 Microsoft Office core suite applications and 2007 Microsoft Office servers
Description of the Project 2007 hotfix package: August 26, 2008
Description of the Project Server 2007 hotfix package: August 26, 2008
Description of the Windows SharePoint Services 3.0 hotfix package: August 26, 2008
Description of the SharePoint Server 2007 hotfix package: August 26, 2008
http://support.microsoft.com/kb/956056 (not published yet)