Windows LiveWindows Live™
 
 
Hans's profileMicrosoft EPM blogBlog Tools Help

Microsoft EPM blog

Everything about the Microsoft Enterprise Project Management solution
September 25

EPM2007 Server-Side Events a Statusing Example

Please find below the slideshow of the webcast I've presented on 18/08/2008
 
Find below a code sample on how you can use the Project Server eventing service to extend the Statusing workflow in EPM 2007.
The code sample below will save updates to Actual Work from the My Tasks page in PWA into a custom DB. The information from the custom DB can now be used as export source or for reporting on pending task updates.
Remark that this is not the full code sample from what I've shown during the webcast. Due to a NDA, I am not allowed to share the entire code.
But if you have any questions, leave me a comment.
 
using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Xml;
using System.Configuration;
using Microsoft.Office.Project.Server.Events;
using Microsoft.Office.Project.Server.Library;

namespace EPMStatusingEventHandler
{
    public class EPMStatusingEventHandler : StatusingEventReceiver
    {
        // When the Save button is hit, save the changes into the custom DB.
        public override void OnStatusUpdated(PSContextInfo contextInfo, StatusUpdatePostEventArgs e)
        {
            try
            {
                // Read the connection information from the application file
                string dbServer = ConfigurationManager.AppSettings["dbServer"];
                string dbName = ConfigurationManager.AppSettings["dbName"];
                string tableName = ConfigurationManager.AppSettings["tableName"];
                string trustedConnection = ConfigurationManager.AppSettings["TrustedConnection"];
                string sqlUserName = ConfigurationManager.AppSettings["sqlUserName"];
                string sqlPassword = ConfigurationManager.AppSettings["sqlPassword"];
 
                // Connect to the database where the custom webwork table is located
                SqlConnection myConnection = new SqlConnection("user id=" + sqlUserName + ";" +
                                                                "password=" + sqlPassword + ";" +
                                                                "server=" + dbServer + ";" +
                                                                "Trusted_Connection=" + trustedConnection + ";" +
                                                                "database=" + dbName + ";" +
                                                                "connection timeout=30");
                myConnection.Open();
 
                // Create a new SQL command to send SQL queries to the database
                SqlCommand myCommand = new SqlCommand();
                myCommand.Connection = myConnection;
 
                // The statusing changes are passed as a ChangeXML document
                // Create a new xmldocument to load and process the ChangeXML
                XmlDocument statusingUpdates = new XmlDocument();
                statusingUpdates.LoadXml(e.ChangeXml);
 
                XmlNodeList projectList;
                XmlNodeList assnList;
                XmlNodeList periodList;
                string ProjID;
                string AssnID;
                string AWDate;
                string AW;
 
                // Run throught the XML document
                // Retrieve the list of projects
                projectList = statusingUpdates.GetElementsByTagName("Proj");
                foreach (XmlNode proj in projectList)
                {
                    // Retrieve the Project GUID (remove {})
                    ProjID = proj.Attributes.GetNamedItem("ID").Value;
                    ProjID = ProjID.Substring(1, ProjID.Length - 2);
                    // Retrieve list of assignments
                    assnList = proj.ChildNodes;
                    foreach (XmlNode assn in assnList)
                    {
                        // Retrieve Assignment GUID (remove {})
                        AssnID = assn.Attributes.GetNamedItem("ID").Value;
                        AssnID = AssnID.Substring(1, AssnID.Length - 2);
                        periodList = assn.ChildNodes;
                        foreach (XmlNode period in periodList)
                        {
                            // See if the change is a PeriodChange on Actual ork (PID = 251658250)
                            if (period.Name.ToString() == "PeriodChange" & period.Attributes.GetNamedItem("PID").Value.ToString() == "251658250")
                            {
                                // Retrieve date of AW entry and AW
                                AWDate = period.Attributes.GetNamedItem("Start").Value;
                                AWDate = AWDate.Substring(0, 10);
                                AW = period.FirstChild.Value.ToString();
                                // Add changed AW value to DB
                                myCommand.CommandText = "INSERT INTO " + tableName + " (ProjectUID, AssignmentUID, TimeByDay, ActualWork, ApprovalStatus, RegType, LastUpdated) VALUES (" +
                                                            "'" + ProjID + "'," +
                                                            "'" + AssnID + "'," +
                                                            "'" + AWDate + "'," +
                                                            "'" + AW + "'," +
                                                            "1," +
                                                            "0," +
                                                            "GETDATE()" +
                                                            ")";
                                 myCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }
                myConnection.Close();
            }
            catch (Exception ex)
            {

            }
        }
 
    }
}
8:56:44 PM | Add a comment | Read comments (2) | Permalink | Blog it
April 22

Project Server 2007: Server Side Events and Queue jobs


Using server side events, you can easily extend the project server 2007 functionality by writing your own event handlers.
On the other hand, most actions in Microsoft Project 2007 en Project Server 2007 - like Save and Publish - are processed by the Project Server Queue
 
So I was wondering what relationship there was between events being fired and queue jobs being processed.
Depending on what you are trying to do in the Event Handler, it might be important to know when exactly an event is fired. So I did some tests and these are my results.
 
There are two types of server side events in Project Server 2007: PRE events and POSTS events.
 
  • PRE-events (ex. ProjectPublishing): pre-events are fired when the Queue job is about to start. So remark that a pre-event is not fired when you hit for example the save button, but when the Project Save queue job is about to be processed. As a result, there might be a significant delay between the user performing and action, and the eventhandler code to run.
  • POST-events (ex. ProjectPublished): post events are fired when the queue job completed succesfully. Remark that if the queue job fails for some reason, or is cancelled, the post-event will not fire.

Events and Queue jobs are closely related, and events are only fired at 'queue speed'. As a result, server side events cannot be used for real-time user interaction.
This also means, that if the Queue is stuck, or the Project Server Queue service is not running, no servre-side events will be fired.

This is not the end of the story however.

Remark that most actions are processed by the project server queue, but NOT ALL actions! For example, Saving and Submitting updates on the My Tasks page in PWA, are not processed by the queue. But both a pre-event and a post-event is fired for these actions! So I did some tests to figure out when they are fired.

  • PRE-event (ex. StatusingStatusUpdating): a pre-event for an action, not processed by the queue, is fired immediatly when performing the action
  • POST-event (ex. StatusingStatusUpdated): a post-event for an action, not processed by the queue, is fired immediatly after the action is completed.

Server-side events for actions not processed by the queue are fired independent from the project server queue. This means that even is the Project Server Queue service is not running, events for these actions are still fired. So for these actions, you can use events for real-time user interaction.
Another example of these events is: StatusingStatusSubmiting and StatusingStatusSubmitted.

Due to lack of time, I don't have a complete list of events (not) related to the project server queue.

More info about writing event handlers can be found in the SDK or on MSDN: http://msdn2.microsoft.com/en-us/library/ms469450.aspx

11:36:50 PM | Add a comment | Permalink | Blog it
April 03

EPM2007 and SQL Server 2005: error message when building OLAP cube


Setting up OLAP cubes for Project Server 2007 on SQL Server 2005 Analysis Services can be a real pain.

I followed the TechNet article and all guidelines on Brian Smith's Project Support WebLog (which contains all possible errors you might run into when setting up OLAP cubes on Project Server 2007), and still I was not able to build the OLAP cube. Project Server 2007 kept telling me:

Failed to connect to the Analysis Services server <SERVER>. Error: Your permissions on the server computer do not allow you to administer this Analysis server. Error: Your permissions on the server computer do not allow you to administer this Analysis server. Error:

I have setup OLAP by creating a new Repository database in SQL 2005 and running the script available on TechNet for creating the tables.You have to add the SSP user to the SQLServer2005MSOLAPUser$<SERVERNAME>$MSSQLSERVER group on the Analysis Services server and grant this group db_owner access to the repository database. Rebooting the servers did not solve the problem.

Turns out that there was something wrong with permission propagation on the SQL Server. For some reason, Analysis Services does not recognize the SSP account as db_owner on the Repository database.

I had to grant the SQL login for the SSP user also db_owner permissions on the Repository database (even though the SSP user already had this permissions as member of the SQLServer2005MSOLAPUser$<SERVERNAME>$MSSQLSERVER group).

The server on which I had this problem was running Windows Server 2003 R2 SP2 + SQL Server 2005 SP2.
I was able to reproduce this problem on a virtual environment with Windows Server 2003 R2 SP2 + SQL Server 2005 SP2. I didn't had this problem on a Windows Server 2003 R2 SP1 + SQL Server 2005 SP1 environment. So it might be related to SP2, but I can't confirm this.

1:08:23 PM | Add a comment | Permalink | Blog it

Custom Fields: Formula limitations


When you create a custom field in Microsoft Project and you populate this field by using a formula, you should know about some limitations that you might run into if your formula is quite long.

  • maximum numbers of characters (spaces included) in the formula: 4094
  • maximum numbers of nested IIf 's: 14
  • maximum length of the return string (in case of a text field): 256

These limitations apply to both local fields (MS Project standard) and Enterprise fields (project server).

These limitations apply to both MS Project 2003 and MS Project 2007.

1:05:59 PM | Add a comment | Permalink | Blog it
 

Archives