Check out my new blog at

Thursday, December 16, 2010

Microsoft Project: TimeScaleData Method from .NET without GC.Collect

Project has a bug where accessing time scaled values causes an exception after a few calls.  You can get around it by calling GC.Collect() and GC.WaitForPendingFinalizers() but this is super slow.  Here is an example of what that looks like.

   1: TimeScaleValues workValues = assignment.TimeScaleData(startDate, endDate, pjAssignmentTimescaleDataType, PjTimescaleUnit.pjTimescaleDays, 1);
   3: for (int i = 1; i <= days; i++)
   4: {
   5:     try
   6:     {
   7:         string valueData = workValues[i].Value.ToString();
   9:         if (string.IsNullOrEmpty(valueData))
  10:         {
  11:             continue;
  12:         }
  14:         totalTSV = totalTSV + decimal.Parse(valueData);
  16:     }
  17:     catch
  18:     {
  19:         Marshal.FinalReleaseComObject(workValues);
  20:         GC.Collect();
  21:         GC.WaitForPendingFinalizers();
  23:         i = 1;
  24:         totalTSV = 0;
  26:         workValues = assignment.TimeScaleData(startDate, endDate, pjAssignmentTimescaleDataType, PjTimescaleUnit.pjTimescaleDays, 1);
  27:     }
  28: }


Here is a glorious hack that involves injecting a temporary macro into the Global.MPT and then calling the macro via reflection to have the TimescaleData method be called inside Project to avoid the problem.

First, inject this macro:

Public Sub GetAssignmentTSV(ByVal projectName As String, ByVal taskIndex As Long, ByVal assignmentIndex As Long, ByVal startDate As String, ByVal endDate As String, ByVal tsvType As Long, byref totalTSV As Variant)

    Dim tsv As TimeScaleValues

    Set tsv = Application.Projects(projectName).Tasks(taskIndex).Assignments(assignmentIndex).TimeScaleData(startDate, endDate, tsvType , pjTimescaleDays)
    Dim t As TimeScaleValue
    For Each t In tsv
        totalTSV = totalTSV + Val(t.Value)
End Sub

By doing this (it attempts to clean up old version of the macro first):

   1: private void InjectTSVMacro()
   2:         {
   3:             try
   4:             {
   5:                 List<VBComponent> removeComponents = new List<VBComponent>();
   7:                 foreach (VBComponent component in _Application.VBE.VBProjects.Item(1).VBComponents)
   8:                 {
   9:                     int startLine = 0;
  10:                     int startColumn = 0;
  11:                     int endLine = 0;
  12:                     int endColumn = 0;
  14:                     if (component.CodeModule.Find("GetAssignmentTSV", ref startLine, ref startColumn, ref endLine, ref endColumn, false, false, false))
  15:                     {
  16:                         removeComponents.Add(component);
  17:                     }
  18:                 }
  20:                 foreach (VBComponent t in removeComponents)
  21:                 {
  22:                     _Application.VBE.VBProjects.Item(1).VBComponents.Remove(t);
  23:                 }
  25:                 _VBAModule = _Application.VBE.VBProjects.Item(1).VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
  27:                 _VBAModule.CodeModule.AddFromString(Resources.TSVMacro);
  29:                 _GetTSVMethod = TSVMethod.MacroInjection;
  30:             }
  31:             catch (Exception exception)
  32:             {
  33:                 QualityFx.WriteException(exception);
  34:                 _GetTSVMethod = TSVMethod.ObjectModel;
  35:             }
  36:         }


Then call the macro like this:

   1: return RunTSVMacro(_Application,
   2:          new object[]
   3:              {
   4:                  "GetAssignmentTSV",
   5:                  syncParameters.Project.Name,
   6:                  task.ID,
   7:                  assignmentId,
   8:                  startDate.ToString(),
   9:                  endDate.ToString(),
  10:                  (int) pjAssignmentTimescaleDataType,
  11:                  totalTSV
  12:              });


RunTSVMacro looks like this:

   1: public decimal RunTSVMacro(Application application, object[] args)
   2: {
   3:     ParameterModifier parameterModifier = new ParameterModifier(TSV_MACRO_NUMBER_OF_ARGS);
   5:     parameterModifier[TSV_MACRO_OUT_ARG] = true;
   7:     ParameterModifier[] parameterModifiers = { parameterModifier };
   9:     application.GetType().InvokeMember("Run",
  10:                                        BindingFlags.Default | BindingFlags.InvokeMethod,
  11:                                        null,
  12:                                        application,
  13:                                        args,
  14:                                        parameterModifiers,
  15:                                        null,
  16:                                        null);
  18:     return (decimal)args[TSV_MACRO_OUT_ARG];
  19: }

The two constants are:

   1: private const int TSV_MACRO_NUMBER_OF_ARGS = 8;
   2: private const int TSV_MACRO_OUT_ARG = 7;

The RunTSVMacro method has to use the parameter modifier to let reflection know that the 7 argument is an out argument because you can call a VBA function using Project’s Run method.

Hope this helps.

Friday, November 12, 2010


Now, I am not a fan of VB.NET (mainly becase I VB’ed myself out when I was at Pacific Edge) to start with, but if you have to write in it, it is really important to follow a good set of development standards.  Good development standards usually include a clause about using whitespace to make code more read-able.  The following is an piece of code I saw (blurred because I am demonstrating a point not beating somebody up).  Not only is there not enough whitespace, the one blank line in the ENTIRE method is just random.



I kinda wish people were capable of putting up dialogs when they “encounter an internal error”, like this dialog Visual Studio gave me a few minutes ago:


Of couse this dialog is really bad because while it suggests I save all files and exit, I have no way of doing that.  Kinda of like when people have “internal errors” and become unstable: you really can’t save them either. 

Thursday, November 11, 2010


I am still here.  For the past 16 months I have been working on a new product.  We are not quite ready to announce publicly but I will say what we have built is very exciting (to a certain crowd, of course).

My hobby projects have taken the way-back seat to product development.  Recently, however, I made time to update the CSharp Header Designer and a new version (actually derivative) of the Code Gallery Download Report.  After working on the same product for so long, I just had to get some other stuff in play to keep my sanity.

CSharp Header Designer 2.0

The original CShap Header Designer allowed you to apply a header to a set of source files, but it didn’t remember which files you applied the header to.  It also didn’t understand anything about Visual Studio projects and solutions so you couldn’t simply specify a header and a solution/project and ask the app to apply the header to all source files in the solution/project.  I personally found this a bit annoying so I rewrote it to understand solutions and projects.  Now instead of just having a “.HeaderX” file, you have a “.HeaderXProject” that is based on a Visual Studio (only 2008 right now) project or solution:


You can add additional source files that are not part of the solution or project using the Source File Selection Dialog:


Also, one of the other contributors (laedit) added auto complete for auto fields, which is cool:


Check it out and let me know what you think.

Code Gallery Tools App

The Code Gallery Download Report originally was just an app designed to give you a consolidated download count for all Code Gallery resources and releases for all files.  I built it because I wanted to get some experience with .NET’s support of feeds (SyndicationFeed Class). All of the resource releases are availabe via a feed so I was able to learn the RSS format and parse out the download details for each release to give a count:


The new version adds support for monitoring downloads and discussions, complete with optional system tray messages that keeps you up to date with your Code Gallery resources.  Also, the new app gives you a discussion browser for all of your Code Gallery Resources, with highlighting!




Anyway, coming soon! A few more features and some testing to do yet.

Hope all is well.


Thursday, February 04, 2010


If you get the error message CustomReportingCustomFieldMetadataChangeMessageFailed after calling UpdateCustomFields, make sure you actually changed something on each custom field row or call GetChanges on the dataset before passing the CustomFieldsDataSet to UpdateCustomFields (which, by the way, is standard practice unless you think you have changed every row).  I THOUGHT I was making a change on each row, but it turns out I wasn’t and I was seeing this in ULS:

02/04/2010 11:55:28.94     Microsoft.Office.Project.Server (0x2328)    0x229C    Project Server                    Project Server Reporting          9dyq    Critical    Standard Information:PSI Entry Point:   Project User: SUPERDEV\administrator  Correlation Id: 85cc626e-252e-41e7-b72a-927f87b1fe87  PWA Site URL: http://monsterdev/PWADev  SSP Name: SharedServices  PSError: ReportingCustomFieldMetadataChangeMessageFailed (24002) RDS: The request to synchronize change(s) to custom field CustomFieldTypeUID='f5d9befe-1e11-4b41-a0ff-baec4adcae9e'. ChangeType='Alter' failed.  Message: 'ReportingCustomFieldMetadataChangeMessageFailed'  Error:Unable to transfer the custom field metadata change. The custom field 'EVMS Entity Type' was subjected to an unsupported change. Undefined, ModificationDate    85cc626e-252e-41e7-b72a-927f87b1fe87

The interesting bit is this “Undefined, ModificationDate”, which was on the custom field that wasn’t actually changed.  It appears that the last modified date was undefined (DB Null) on the dataset, which I found odd because that field is modified by the system, not by the caller to the PSI.  So, the fix is to ensure you only pass the PSI custom field records with changes or by the time the reporting database metadata processor gets to it, it finds a custom field that was “updated” (but not really, because the record had no changes and therefore the last modified date wasn’t change and in fact is null), and it throws up.



Friday, January 29, 2010

DB Connection Library on Code Gallery

Jon Henry asked about how to change connection strings dynamically in an application so I packaged up my DB Connect Library and put it on CodePlex.


Tuesday, January 05, 2010

Still Here

Folks have been asking what happened to the blog.  I am on a pretty intense product development cycle right now, but I will be back soon!


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.