Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

May, 2008

  • Brian Smith's Microsoft Project Support Blog

    How to ensure your accepted updates get published in Project Server 2007

    • 38 Comments

    In Project Server 2003 updates needed to be made with Project Professional , and then when Project was closed an auto publish would happen – if you wanted it to or not.  Some people wanted it, some didn’t.  In 2007 as we don’t need to use Project Professional and this can happen purely on the server then we leave the choice of when to publish up to you. 

    One way you can simulate the 2003 behavior is to use a server-side event handler to act on the Statusing.OnApplied method, and carry out a publish of the project via the PSI.  This would then ensure that all accepted updates were reflected in PWA.  The following code samples is based on the SDK TestEventHandler, and apart from the code, would also require web references to the Project and LoginWindows web services.  I am also not using impersonation, so my SSP Administrator (the account that would be running the Event services) does need to be a user in PWA with the right permissions to publish the projects.  You could use the same techniques as I recently published for impersonation if you did not want to give any PWA permissions to this account.  If multiple publish events hit the queue at one time then most will get skipped for optimization – but if you are processing a great deal of updates you should consider the load this extra publish work will put on your server.  I use the “post” rather than the “pre” event so that even if the event handler fails it will not block or cancel the event.  Obviously my writing to the event log is optional too – just helped me to see it was working.

    As usual with MSDN blog postings the code is supplied “as-is”, with no warranties or support and could probably do with some better exception handling – but hopefully for any customers wanting to get auto publishing this will be a help.  You will need to replace servername and pwa with your own servername and pwa instance names.

    using System;
    using System.Net;
    using System.Diagnostics;
    using Microsoft.Office.Project.Server.Events;
    using Microsoft.Office.Project.Server.Library;
    
    namespace TestEventHandler
    {
        public class MyPublishingEventHandler : StatusingEventReceiver 
        {
            const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
            const string PROJECT = "_vti_bin/PSI/Project.asmx";
            private static WebSvcLoginWindows.LoginWindows 
                loginWindows = new 
                    WebSvcLoginWindows.LoginWindows();
            private static WebSvcProject.Project project =
                new WebSvcProject.Project();
            private string baseUrl = "http://servername/pwa/";
     
            public override void  OnApplied(PSContextInfo contextInfo, 
                StatusingPostApplyEventArgs e)
    
            {
                 base.OnApplied(contextInfo, e);
                
                loginWindows.Url = baseUrl + LOGINWINDOWS;
                loginWindows.Credentials = 
                    CredentialCache.DefaultCredentials;
                project.Url = baseUrl + PROJECT;
                project.Credentials = 
                    CredentialCache.DefaultCredentials;
    
                // I don't do a full publish 
                // You could change the third parameter to true if you wanted to
                Guid jobUid = Guid.NewGuid();
                project.QueuePublish(jobUid, e.ProjectID, false, "");
    
                // Create an EventLog instance and assign its source.
                EventLog myLog = new EventLog();
                myLog.Source = "Auto Publish Event Handler";
    
                // Get information from the event arguments, and 
                // write an entry to the Application event log. 
                string userName = contextInfo.UserName.ToString();
                string projectGuid = e.ProjectID.ToString();
                int eventId = 3945;
                string logEntry;
    
                logEntry = "User: " + userName + "\nProject UID: "
                    + projectGuid + " has been queued for publish.";
                myLog.WriteEntry(logEntry, 
                    EventLogEntryType.Information, eventId);
    
                }
            
        }
    }

    The SDK description of the Event Handlers, and links to the SDK samples can be found here.

  • Brian Smith's Microsoft Project Support Blog

    TechEd 2008 Preview – Part 1 – Project Server 2007 Line of Business Integration with the Business Data Catalog

    • 5 Comments

    This is the first of my pre-TechEd 2008 postings – and you may already have seen one that Christophe posted a few days ago.  This one is part of the same session I am doing with Christophe next Thursday afternoon (not Tuesday as I said earlier) in Orlando and the scenario here is accessing both AdventureWorks data (from the sample database) and Project Server 2007 data using the Business Data Catalog (BDC). 

    To connect the two sets of data I am using a Project level custom field to hold the Vendor ID, which is the same code used in AdventureWorks to identify vendors.  More on this link in my next post where I have a database CLR Trigger in C# that updates Project Server with new vendors – and Christophe’s code mentioned above stops “unauthorized” users making changes to the specific lookup table.

    A picture is certainly worth a thousand or more words – so here is a sample of the kind of this this scenario delivers.  This is a web part page with a Business Data List and a Business Data Item part added.  Selecting from the list allows display of more details of the specific project and associated vendor – as well as a few actions.

     image

    We can see details of the actual work and cost (from Project Server) and the vendor name, city and location (from AdventureWorks).  The actions on the top web part allow us to search for the Project Owner on Live Search, Drill Down to Project on PWA, and finally to Map the Vendor Location using Live Maps – and here is what you get using that last option…

    image

    As you can see from the URL it is using data from the BDC as parameters in the search – the other actions use a similar process.  In this example as the AdventureWorks street addresses are fictitious I just use the zip code and city.

    I have attached the XML of the definition file, and you will need to make a few edits to fit your server names and you will also need to have a Project level Number custom field to hold the VendorID.  I am pulling data from the reporting database views and cross joined to the AdventureWorks database as you will see from the XML.

    When I started working with the BDC I found it easiest to look at the XML files from the AdventureWorks sample first, then also took a look at Christophe’s search example for project data and once I understood how it all worked it made it easier to work with the Catalog Definition Editor.  The cool thong about the definition editor is that it allows you to execute the “finders” to ensure everything is working as planned.

    image

    and even the actions…

    image

    In the next week or two I aim to post a video walking through creation of some of this – but I am sure with the XML and looking at the other samples already out there you should soon be able to get this kind of thing working.  As a final couple of ideas for you I also show a couple of list to which I have added a new column containing “Business Data” and then defined which elements I want to display.  This then adds data from Project and AdventureWorks to list data!

    First is a calendar list item

    image

    where my project meeting item has the actions available

    image

    including profile information

    image

    The same kind of thing can be surfaced in more regular custom lists…

    image

    and again all the actions are available for each project.

    One thing to remember is that in this example I am just pulling data based on the account running the web application so you will want to consider security in more depth than I have.  Using a web service instead of SQL as the datasource for the BDC may give you more control in this respect – although the standard PSI web services cannot be consumed directly.  Finally the BDC is just surfacing the data – it isn’t replicated in any way and is not stored in the content database.

  • Brian Smith's Microsoft Project Support Blog

    Which master pages can you edit in Project Server 2007?

    • 15 Comments

    A recent support incident showed that some of our documentation didn’t make it clear just what you can and can’t do with master pages.  There are two different types of sites in Project – PWA (the Project Web Access sites) and PWS (the project workspace sites).  For the PWA sites you are limited to making use of the default.master we already supply to give any extra pages the same look and feel, but you are not able to edit these default.master pages.  We also block SharePoint Designer from opening these sites – so you cannot use this tool against a PWA site.  You can however use SharePoint Designer against the PWA workspace sites – and edit the default.master.

    The reason for this difference is that it would be easy to break our application by editing our pages – and this would not make life good for a support engineer (and indeed for the customers who manage to break things!).  Also remember that even though the workspaces can be modified you shouldn’t add extra instances lists of the specific project type lists of issues, risks, dependencies and documents.  Details from these default lists feed through to the reporting database – if there are duplicates then this will break the workspace reporting feature.

    This topic is covered at http://msdn.microsoft.com/en-us/library/ms504195.aspx and this highlights another great way you can add to the sum of human knowledge on MSDN.  BruceVB added some community content based on his experience – and our own Jim Corbin has added a further comment.  Although I wouldn’t necessarily agree that our original document is incorrect – it is certainly not complete in telling you what you can and cannot do.  But even being able to use our default.master on new pages does make it easier to build custom Project Web Access solutions – even if you cannot edit the default.master.

    If you do try and update the PWA default.master through Site Actions, Site Settings then you may see the error message "The enablesessionstate attribute on the page directive is not allowed in this page." 

    You would need to reset to the site definition to get the site working again. 

    Please do use the community content features on MSDN – it certainly does help us to understand how we can improve our content – and thanks again BruceVB for bringing this particular problem to our attention.

  • Brian Smith's Microsoft Project Support Blog

    Project Server 2007: Have you ever got stuck in the timesheet import page?

    • 4 Comments

    I was working on a case today where a bug in one place makes something break elsewhere. And the added challenge here is the original problem probably goes un-noticed so isn’t obviously a problem.

    If you have ever tried to import a timesheet on the My Tasks page, and the screen flashed but nothing happened – and the only way out was to press Cancel then you may have this problem.  The timesheet does not restrict the tasks that you can add in the same way they show in My Tasks.  Either through Add Lines, or using the automatic method from Server Settings, Timesheet Settings and Defaults page (By default, timesheets will be created by using:  Current task assignment) you could end up with lines on your timesheet where you are not the assignment owner.  It is this that breaks the import.  You will also notice that these tasks do not appear on your My Tasks page.

    In many cases I’m guessing that you do expect to be the assignment owner, and some earlier (now fixed) bugs may have led to projects and templates having the incorrect assignment owner for your assignments.  The workaround is therefore to either correct the assignment owner and republish – or remove the tasks from the timesheet if the assignment owner is valid (and not you).

    For those searching the following are the errors at various levels that you would find in the ULS logs as a result of this problem.  Not great reading – but the search engines appreciate it.

    Exception Level Log
    Exception occurred in method Statusing.ImportTimesheet System.ArgumentException: Statusing - Cannot create new Saved Task for resource's task modifications.  Parameter name: taskData     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.CreateSavedTask(PlatformContext context, DalDataAccess dataaccess, ISvrDocEdit doc, ITaskData taskData)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.ImportTimesheet(Guid periodUID)     at Microsoft.Office.Project.Server.WebService.Statusing.ImportTimesheet(Guid periodUID)

    Verbose Level Log
    Error is: GeneralUnhandledException. Details: Attributes:  System.ArgumentException: Statusing - Cannot create new Saved Task for resource's task modifications.  Parameter name: taskData     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.CreateSavedTask(PlatformContext context, DalDataAccess dataaccess, ISvrDocEdit doc, ITaskData taskData)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.ImportTimesheet(Guid periodUID)     at Microsoft.Office.Project.Server.WebService.Statusing.ImportTimesheet(Guid periodUID)  . Standard Information: PSI Entry Point: Statusing.ImportTimesheet  Project User: REDMOND\pkmuser2  Correlation Id: f5af0b6e-a9be-4b88-88fa-3529021a1696  PWA Site URL: http://servername/pwa  SSP Name: SharedServices1  PSError: GeneralUnhandledException (42)

    Medium Level Log
    PWA:http://servername/pwa, SSP:SharedServices1, User:DOMAIN\User, PSI: Statusing.ImportTimesheet  Undefined Attributes: PSError: GeneralUnhandledException  Undefined attributes list: System.ArgumentException: Statusing - Cannot create new Saved Task for resource's task modifications.  Parameter name: taskData     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.CreateSavedTask(PlatformContext context, DalDataAccess dataaccess, ISvrDocEdit doc, ITaskData taskData)     at Microsoft.Office.Project.Server.BusinessLayer.Statusing.ImportTimesheet(Guid periodUID)     at Microsoft.Office.Project.Server.WebService.Statusing.ImportTimesheet(Guid periodUID)

    Verbose Level Log
    PWA:http://servername/pwa, SSP:SharedServices1, User:DOMAIN\User, PSI: Statusing.ImportTimesheet  PSI Entry Point: Statusing.ImportTimesheet  Project User: DOMAIN\User Correlation Id: f5af0b6e-a9be-4b88-88fa-3529021a1696  PWA Site URL: http://servername/pwa  SSP Name: SharedServices1  PSError: Success (0) XML: <errinfo><general><class name="An unhandled exception occurred in Statusing.ImportTimesheet."><error id="42" name="GeneralUnhandledException" uid="862074be-9315-4c83-9a1f-66053d785061" /></class></general></errinfo>

    Technorati Tags:
  • Brian Smith's Microsoft Project Support Blog

    TechEd 2008 Preview – Part 2 – Project Server 2007 Line of Business Integration using CLR Triggers

    • 3 Comments

    In yesterday’s posting the BDC was used to present data from both Project Server and the AdventureWorks database with the join being the Vendor ID from the Purchasing.Vendors table.  To allow this to happen we need a custom field and lookup table in Project Server that has the values for the vendors so that we can make the association.  One way to do this (and there are many) is to use a CLR trigger – and that is another part of Christophe and my presentation next Thursday at TechEd.

    SQL Server 2005 introduced the capability to use Common Language Runtime procedures within database entities.  CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.  This example uses C# within a trigger to make PSI calls and insert values to a Project Server lookup table.  As with any programming task, just because you can do something a particular way does not necessarily mean you should, and you certainly wouldn’t want to use the code here to make very frequent updates between systems – but for occasional updates – such as adding a new vendor in a small application it may fit the bill.

    There are a number of hoops to jump through to get CLR integration running with SQL Server – and as it does open up some security concerns you will need to fully understand the consequences of these changes before making them.  They are full described, and some alternative options presented, in the SQL Server Books Online starting with the Introduction linked above and specifically dealing with security considerations here.  Please read these articles – then the following steps will make more sense.

    The first step is to set the database instance to CLR enabled – and this uses sp_configure:-

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'clr enabled', 1;

    GO

    RECONFIGURE;

    GO

    Next is to give the dbo UNSAFE ASSEMBLY permissions:-

    use master

    GRANT UNSAFE ASSEMBLY To "domain\user"

    go

    and then set the database property TRUSTWORTHY to true

    ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON

    an alternative approach here is to sign the assembly with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

    When using calls to Web Services, as we will be with the PSI, you also need to deploy the serialization assembly – so this involves setting the option to Generate serialization assembly and then configuring PostDeployScript.sql and PreDeployScript.sql to make the deployment.

     

    clip_image002

     

    PreDeployScript.sql

    IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'AWTrigger.XmlSerializers') 
        DROP ASSEMBLY [AWTrigger.XmlSerializers]

    PostDeployScript.sql

    CREATE ASSEMBLY [AWTrigger.XmlSerializers] from
    'C:\TechEd\LOB\Brian\AWTrigger\bin\Debug\AWTrigger.XmlSerializers.dll'
    WITH PERMISSION_SET = SAFE

    Finally piece of setup in this example is that I created a new table in AdventureWorks and then used just a normal SQL trigger to copy data from the Vendor table to my table.  This was a workaround for what I later found was a bug with VS 2008 in that it cannot deploy automatically to entities in schema other than dbo – so the Purchasing.Vendor table would take the trigger directly.  I could have manually deployed to work around this – but as I already had the workaround working I haven’t done this.  For completeness this is the SQL to create my extra table called AWTrigger, and is followed by the SQL to add the SQL trigger to the Purchasing.Vendor table.

    USE [AdventureWorks]
    GO
    /****** Object:  Table [dbo].[AWTrigger]    Script Date: 05/20/2008 12:36:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[AWTrigger](
        [VendorID] [int] NULL,
        [VendorName] [varchar](50) NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING ON

    USE [AdventureWorks]
    GO
    /****** Object:  Trigger [Purchasing].[copyToAWTrigger]    Script Date: 05/20/2008 12:36:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    CREATE TRIGGER [Purchasing].[copyToAWTrigger]
       ON  [Purchasing].[Vendor]
       AFTER INSERT
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for trigger here
        declare @vendorid int;
        declare @vendorname varchar(50);

        select @vendorid=i.VendorID from inserted i;
        select @vendorname=i.Name from inserted i;

        insert into AWTrigger (VendorID, VendorName) values(@vendorid, @vendorname)

    END

    Now to the real code!  In VS 2008 you can create a project of type SQL-CLR

    image

    and you will set up a database connection.  In this solution I also have web references added for the LoginWindows and LookupTable web services.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Net;
    using System.Security.Principal;
    using Microsoft.SqlServer.Server;
    
    public partial class Triggers
    {
        // Enter existing table or view for the target and uncomment the attribute line
        // Using another table rather than messing with the Vendor table to work around known bug with triggers in other schema
        // Could have deployed trigger manually.
        [Microsoft.SqlServer.Server.SqlTrigger(Name = "LUTTrigger", Target = "AWTrigger", Event = "FOR INSERT")]
        public static void LUTTrigger()
        {
            #region Setup
    
            const string PSI_URI = "http://W2K3/pwa/_vti_bin/psi/"; // <<--Change to match your project server and directory.
            const string LOBI_NAME = "AW - LUTTrigger";
    
            WindowsIdentity winID = SqlContext.WindowsIdentity;
    
            // Set up the services.
            AWTrigger.wsLoginWindows.LoginWindows loginWindows = new AWTrigger.wsLoginWindows.LoginWindows();
            AWTrigger.wsLookupTable.LookupTable lookupTable = new AWTrigger.wsLookupTable.LookupTable();
            loginWindows.Url = PSI_URI + "loginWindows.asmx";
            lookupTable.Url = PSI_URI + "lookuptable.asmx";
           
            loginWindows.UseDefaultCredentials = true;
            lookupTable.UseDefaultCredentials = true;
    
            CookieContainer cookies = new CookieContainer();
            loginWindows.CookieContainer = cookies;
    
            AWTrigger.wsLookupTable.LookupTableDataSet dsLookupTable;
    
            // Guid for AW-Vendor Lookup Table set and then put into array
            Guid lutGuid = new Guid("E6342609-6A5D-4D05-94D1-519D0901F566"); 
     
    // You will need to change the UID used here – and good practice would be to get this from a .config file.
            
            Guid[] lutList = new Guid[] { lutGuid };
            bool autoCheckOut = false;
            string msg = string.Empty;
    
            #endregion
    
            try
            {
                // Attempt to log in using Windows Credentials
                if (!loginWindows.Login())
                {
                    msg = string.Format("{0}: User {1} CANNOT login using Windows Credentials: {2}"
                        , LOBI_NAME, winID.Name, loginWindows.Url);
                    WriteEvent(msg, EventLogEntryType.FailureAudit, 8989);
                    return;
                }
    
                // Get the VendorID and Name from the inserted row
                int vendorID;
                string vendorName;
                SqlCommand command;
                SqlTriggerContext triggContext = SqlContext.TriggerContext;
                SqlPipe pipe = SqlContext.Pipe;
                SqlDataReader reader;
    
                using (SqlConnection connection
                    = new SqlConnection(@"context connection=true"))
                {
                    connection.Open();
                    command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
                    reader = command.ExecuteReader();
                    reader.Read();
                    vendorID = (int)reader[0];
                    vendorName = (string)reader[1];
                    reader.Close();
                }
                // Get a dataset for our vendor lookup table and add out row – then update
                dsLookupTable = lookupTable.ReadLookupTablesByUids(lutList, autoCheckOut, 1033);
    
                AWTrigger.wsLookupTable.LookupTableDataSet.LookupTableTreesRow rowLookupTableTree =
                    dsLookupTable.LookupTableTrees.NewLookupTableTreesRow();
    
                Guid lt_struct_uid = Guid.NewGuid();
                rowLookupTableTree.LT_STRUCT_UID = lt_struct_uid;
                rowLookupTableTree.LT_UID = lutGuid;
                rowLookupTableTree.LT_VALUE_NUM = (decimal)vendorID;
                rowLookupTableTree.LT_VALUE_DESC = vendorName;
                dsLookupTable.LookupTableTrees.AddLookupTableTreesRow(rowLookupTableTree);
    
                lookupTable.CheckOutLookupTables(lutList);
                bool validateOnly = false;
                lookupTable.UpdateLookupTables(dsLookupTable, validateOnly, autoCheckOut, 1033);
                bool forceCheckIn = false;
                lookupTable.CheckInLookupTables(lutList, forceCheckIn);
            }
            catch(Exception ex)
            {
                msg = string.Format("{0}: EXCEPTION {1}", LOBI_NAME, ex.Message);
                WriteEvent(msg, EventLogEntryType.Error, 8989);
            }
        }
    
        #region Helper Methods
    
        private static void WriteEvent(string msg, EventLogEntryType logEntryType, int eventId)
        {
            EventLog myLog = new EventLog();
            myLog.Source = "Adventure Works LOBI";
    
            string message = msg;
            myLog.WriteEntry(msg, logEntryType, eventId);
        }
    
        #endregion
    
    }

    The code isn’t totally robust, and will fail if the lookup table is checked out – and the code that Christophe posted earlier this week on controlling lookup table updates helps with that.  It controls who can check out that particular lookup table.  In triggers it is also really important to handle exceptions as you always want the trigger to “work” even when it fails.  Try/Catch and writing errors out to the event log in this case ensures the trigger works and doesn’t undo any of the changes to the original vendor table.

    The test.sql script is used for debug – by actually making an insert that will fire the trigger – thanks Christophe for adding this piece and also the better exception and error handling. 

    DECLARE @AcctNumber nvarchar(10)
    SET @AcctNumber=ROUND(RAND()*1000,0)
    
    INSERT INTO Purchasing.Vendor (AccountNumber, Name, CreditRating, PreferredVendorStatus, ActiveFlag, ModifiedDate)
           VALUES (@AcctNumber, 'Vendor '+@AcctNumber, 1, 1, 1, GETDATE())

    As I was working on this I made good use of Live Search – so thanks to Vineet on the SQL Server team for the very helpful blog posting as well as excellent content from the following KB and TechNet articles.

    http://support.microsoft.com/kb/913668

    CLR Triggers

    http://technet.microsoft.com/en-us/library/ms131093.aspx

    Building Database Objects with Common Language Runtime (CLR) Integration

    http://technet.microsoft.com/en-us/library/ms131046.aspx

    Getting Started with CLR Integration

    http://technet.microsoft.com/en-us/library/ms131052.aspx

    Enabling CLR Integration

    http://technet.microsoft.com/en-us/library/ms131048.aspx

    Part 3 will probably be posted early next week – and will include a server side event handler that uses the OnPublished event to query a project custom field for the type of Project Workspace you would like created for that project.  This will be part of my chalk talk session with Boris Scholl on Wednesday morning on customizing the UI in Project Server 2007.

Page 1 of 2 (10 items) 12