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

  • Brian Smith's Microsoft Project Support Blog

    TechEd 2008 switches tracks from the Developer to the IT Professional

    • 0 Comments

    I really enjoyed TechEd last week – and thanks to all who attended the sessions and came and said “Hi”.  I’m back in the office this week doing proper work – but Christophe is back down in the sunshine of Florida.

    This week Christophe will be joined by Simon Floyd, Michael Jordan and Emmanuel Fadullon at TechEd IT Professionals in Orlando and they will be delivering the following EPM sessions:

    OFC255

    Best Practices: Enabling Innovation Process Management Using Microsoft Office Enterprise Project Management Solution and Microsoft Office SharePoint Server 2007

    Tuesday, June 10 10:30 AM

    In today’s fast-paced global economy, innovation is the key to the next big breakthrough in products, services, and processes. However, businesses are often challenged with facilitating innovation due to cultural, strategic, or logistical pitfalls. This session covers the best practices for fostering a culture of innovation by enabling an organization’s most valuable assets, its people, to actively and easily participate in the innovation process. A live demonstration shows how anyone can capture, investigate, formulate, and evaluate ideas to conclusion using the Microsoft Office Enterprise Project Management solution and SharePoint Server 2007.

    OFC358

    Deploying Microsoft Office Enterprise Project Management Solution 2007 into an Existing Microsoft Office SharePoint Server Environment

    Tuesday, June 10 1:15 PM

    Microsoft Office Enterprise Project Management Solution 2007 (EPM 2007) is a very unique form of Windows SharePoint Services V3. This sessions provides you with best practices, learned in Performance Labs in Redmond, to keep in mind when you want to deploy EPM 2007 into an existing MOSS farm.

    OFC450

    Microsoft Office Project Server 2007 Deployment for High Availability and Scalability

    Wednesday, June 11 1:00 PM

    This session covers the components of the EPM solution and the main considerations when planning for deployments that require high availability, when to scale up and out, points of failure and software/hardware boundaries.

    OFC53-TLC

    Microsoft Office Project Server 2007 Disaster Recovery

    Thursday, June 12 8:30 AM

    Learn how to recover from a catastrophic failure on your SharePoint/EPM farm. This session discusses the options to recover from a loss of any component of your SharePoint/EPM Farm: SQL server, application server, disk, etc. It covers SharePoint/EPM Farm or database restore options including pros and cons as well as a live demo. This session does not cover detailed backup/restore plans and all high availability options.

    Beside attending all these Project Server sessions, you can also meet them at the Project Server booth, don’t be shy!

  • Brian Smith's Microsoft Project Support Blog

    TechEd 2008 Preview – Part 4 – COM Add-in from Project Professional making PSI calls

    • 1 Comments

    This is the last TechEd sample and is a very simple example of a PSI call from Project Pro (or Standard for that matter).  It shows how a user of the client not connected to the server can still make PSI calls.  Obviously they need an account in the PWA they are communicating with.  The sample shows a PSI call to check that the local project name (or any text you want to enter manually) is in use on the Project Server.  The response is shown on the form for the add-in and also put into the text1 field of the project – just to show how local data can also be updated with VSTO applications. 

    The details for creating a VSTO add-in for project were very well covered in Jack Dahlgren's posting http://zo-d.com/blog/archives/programming/making-the-move-from-vba-to-vsto-in-microsoft-project.html posting recently – thanks Jack – so I will just give samples of my code and not repeat the “how to” section.  I also needed web references to the Project and LoginWindows web services.  My code is also in C# so some differences to Jack’s posting. 

    The ThisAddIn.cs file looks like this and is adding a toolbar button and setting the for to show when the button is clicked:-

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;
    using MSProject = Microsoft.Office.Interop.MSProject;
    using Office = Microsoft.Office.Core;
    using System.Windows.Forms;
    
    namespace TechEdComAddIn
    {
        public partial class ThisAddIn
        {
            private Office.CommandBar commandBar;
            private Office.CommandBarButton importButton;
    
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                
                if (commandBar == null)
                {
                    int barPosition = 1;
                    bool isMenuBar = false;
                    bool isTemporary = true;
                    commandBar = Application.CommandBars.Add("ValidateBar", barPosition,
                                                             isMenuBar, isTemporary);
                }
                
                try
                {
                    importButton = (Office.CommandBarButton)commandBar.Controls.Add(
                        Office.MsoControlType.msoControlButton, missing, missing, missing, missing);
                    importButton.Style = Office.MsoButtonStyle.msoButtonCaption;
                    importButton.Caption = "Validate Project Name";
                    importButton.Tag = "Validate Project Name";
                    importButton.TooltipText = "Validates a name for later use in Project Server.";
                    importButton.Click +=
                        new Office._CommandBarButtonEvents_ClickEventHandler(ImportButtonClick);
    
                    commandBar.Visible = true;
                }
                catch (ArgumentException ex)
                {
                    MessageBox.Show(ex.Message, "Error adding toolbar button",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            private void ImportButtonClick(Office.CommandBarButton ctrl, ref bool cancel)
            {
                //ImportDialogBox importDialog = new ImportDialogBox();
                //importDialog.Show();
                
                ProjNameValidate myForm = new ProjNameValidate(); ;
                myForm.Show();
            }
            
           
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
            
            #endregion
        }
    }
     
    The form looks like this:-
     
    image 
    And the code behind:-
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Net;
    using System.Text;
    using System.Windows.Forms;
    
    namespace TechEdComAddIn
    {
        public partial class ProjNameValidate : Form
        {
            private const string URLPREFIX = "http://";
            private const string LOGINWINDOWSWEBSERVICE = "_vti_bin/PSI/LoginWindows.asmx";
            private const string PROJECTWEBSERVICE = "_vti_bin/PSI/Project.asmx";
    
            private string baseUrl = "http://brismithwfe/cal/";
    
    
      
            private static WebSvcLoginWindows.LoginWindows loginWindows =
                new WebSvcLoginWindows.LoginWindows();
            private static WebSvcProject.Project project =
                new WebSvcProject.Project();
    
            public ProjNameValidate()
            {
                InitializeComponent();
                string projName = Globals.ThisAddIn.Application.ActiveProject.Name.ToString();
                int extLocation = projName.LastIndexOf(".mpp");
                textBoxProjectName.Text = projName.Substring(0, extLocation);
            }
    
            private void btnValidateName_Click(object sender, EventArgs e)
            {
                try
                {
                    loginWindows.Url = baseUrl + LOGINWINDOWSWEBSERVICE;
                    loginWindows.Credentials = CredentialCache.DefaultCredentials;
                    project.Url = baseUrl + PROJECTWEBSERVICE;
                    project.Credentials = CredentialCache.DefaultCredentials;
    
                   
                    WebSvcProject.ProjectDataSet dsProject =
                            new WebSvcProject.ProjectDataSet();
                    dsProject = project.ReadProjectList();
                    foreach (WebSvcProject.ProjectDataSet.ProjectRow rowProject in dsProject.Project)
                        if (rowProject.PROJ_NAME == textBoxProjectName.Text.ToString())
                        {
                            lblResponse.Text = "Project name already in use";
                            lblResponse.Visible = true;
                            break;
                        }
                        else
                        {
                            lblResponse.Text = "Project name is OK to use.";
                        }
                    Globals.ThisAddIn.Application.ActiveProject.Text1 = lblResponse.Text;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
    
            }
        }
    }
    
    I don’t think there is anything too complicated.  I trim the .mpp off the local filename and then see if it exists on the server.  Globals is the way to interact with the local application object.
    Once you have that available most of the VBA developers out there should be on home territory.  Take a look at the VSTO stuff – it is very simple to deploy too – as Jack’s article outlines.
    Technorati Tags: , ,
     
     
     
     
     
     
     
  • Brian Smith's Microsoft Project Support Blog

    TechEd Preview – Part 3 – Project Server 2007 Provisioning Project Specific Workspaces automatically

    • 10 Comments

    First day of TechEd today and despite the 3hr time difference from Seattle I still woke up early.  This posting will be part of the subject of the chalk talk I am delivering with Boris Scholl tomorrow (6/4) morning.  If any of you remember the Solution Accelerator for Six Sigma this is part inspired by some custom code that was in that solution – and is designed to allow a Project Manager to select a workspace type from a list in a custom project field – and then when she publishes that type of site will be provisioned from a pre-built template.

    The first part of this is to create some templates for new workspaces.  This is covered in the SDK, but briefly you must base any new template on an unlinked site, then you save as a template, then from the template gallery save to a file (*.stp) and then you use stsadm –o addtemplate to bring the template back in so it can be used for provisioning (and a final IIS reset).  You will also need to use the command stsadm –o enumtemplates to get the internal name (something like _GLOBAL_#2) for each of your templates for later use.

    The next step is creating a lookup table to hold the template details – and it should look something like this:-

    image

     

    I am using the description field to hold the internal name of the template as that is the one used when requesting a site to be provisioned.  You could resolve this in code somewhere – it just seemed easier to me to put it here. You also need to create a Project Level custom field that references this lookup table – and in my example I use the “NotYet” value to mean don’t create a site for me.  The names of the field/table aren’t important – but you will need to know the Guid associated with them.  Also you will need to turn off automatic site creation – as we will be controlling this via an event handler.  I am using Red/Green/Blue for the visual recognition for the demo – obviously ISO 9001, Six Sigma, Administration m- would be more useful types of templates to use.

    Now the event handler.  I’ve thrown a few comments in and also it logs to the application event log the different actions it can take.  It could certainly do with more (read some) exception handling… but it does the job.  Based on the custom field requested it will get the template name from the lookup table description.  If a site already exists it will stop – and if the requested site is already in use it will stop.

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.Text;
    using System.Net;
    using Microsoft.Office.Project.Server.Events;
    using PSLibrary = Microsoft.Office.Project.Server.Library;
    
    
    namespace TechEdEventHandler
    {
        public class MyPublishedEventHandler : ProjectEventReceiver
        {
            private const string URLPREFIX = "http://";
            private const string LOGINWINDOWSWEBSERVICE = "_vti_bin/PSI/LoginWindows.asmx";
            private const string PROJECTWEBSERVICE = "_vti_bin/PSI/Project.asmx";
            private const string WSSINTEROPWEBSERVICE = "_vti_bin/PSI/WSSInterop.asmx";
            private const string LOOKUPTABLEWEBSERVICE = "_vti_bin/PSI/LookupTable.asmx";
            private string baseUrl = "http://brismithwfe/cal/";
            private const int EVENTID = 9191;
            
            private string webTemplateName;
            private Guid projectGuid;
            private Guid codeValue;
            const int PROJECT_CUSTOM_FIELDS_ENTITY_TYPE = 32;
            // We need the Guids for the lookup table and custom field - better practice would be to use a settings file
            private Guid workspaceTemplateLUTGuid = new Guid("b0fc9d01-b716-43d3-aa74-335a3297214b");
            private Guid workspaceTemplateCFGuid = new Guid("98c9444e-1fa2-4f12-ab2b-4cb7e1818738");
    
            private static WebSvcLoginWindows.LoginWindows loginWindows =
                new WebSvcLoginWindows.LoginWindows();
            private static WebSvcProject.Project project =
                new WebSvcProject.Project();
            private static WebSvcWssInterop.WssInterop wssInterop =
                new WebSvcWssInterop.WssInterop();
            private static WebSvcLookupTable.LookupTable lookupTable =
                new WebSvcLookupTable.LookupTable();
    
            public override void OnPublished(Microsoft.Office.Project.Server.Library.PSContextInfo contextInfo, ProjectPostPublishEventArgs e)
            {
                string eventName = "Auto Workspace Creation - OnPublished";
                
                loginWindows.Url = baseUrl + LOGINWINDOWSWEBSERVICE;
                loginWindows.Credentials = CredentialCache.DefaultCredentials;
                project.Url = baseUrl + PROJECTWEBSERVICE;
                project.Credentials = CredentialCache.DefaultCredentials;
                lookupTable.Url = baseUrl + LOOKUPTABLEWEBSERVICE;
                lookupTable.Credentials = CredentialCache.DefaultCredentials;
                wssInterop.Url = baseUrl + WSSINTEROPWEBSERVICE;
                wssInterop.Credentials = CredentialCache.DefaultCredentials;
    
                projectGuid = e.ProjectGuid;
    
                WebSvcWssInterop.WssServersDataSet dsWssServersDataSet = 
                    new WebSvcWssInterop.WssServersDataSet();
    
                WebSvcWssInterop.WssSettingsDataSet dsWssSettingsDataSet =
                    new WebSvcWssInterop.WssSettingsDataSet();
    
                WebSvcWssInterop.ProjectWSSInfoDataSet dsProjectWSSInfoDataSet =
                    new WebSvcWssInterop.ProjectWSSInfoDataSet();
    
                // If rowcount is greater than zero then a site already exists for the project - so exit
                dsProjectWSSInfoDataSet = wssInterop.ReadWssData(projectGuid);
                int rowCount = dsProjectWSSInfoDataSet.ProjWssInfo.Rows.Count;
                if (rowCount == 0)
                {
    
                    WebSvcProject.ProjectDataSet dsProject =
                        new WebSvcProject.ProjectDataSet();
                    // We are getting the project dataset - but only for the entity type 32 - custom fields
                    dsProject = project.ReadProjectEntities(projectGuid, PROJECT_CUSTOM_FIELDS_ENTITY_TYPE, WebSvcProject.DataStoreEnum.WorkingStore);
                    // The we look for the workspace template field - and get the code value
                    foreach (WebSvcProject.ProjectDataSet.ProjectCustomFieldsRow rowProjectCF in dsProject.ProjectCustomFields)
                        if (rowProjectCF.MD_PROP_UID == workspaceTemplateCFGuid)
                        {
                            codeValue = rowProjectCF.CODE_VALUE;
                        }
                    // Using the Guid for the lookup table we search for the code value 
                    Guid[] ltUidList = new Guid[] { workspaceTemplateLUTGuid };
                    WebSvcLookupTable.LookupTableDataSet dsLookupTable =
                        new WebSvcLookupTable.LookupTableDataSet();
                    dsLookupTable = lookupTable.ReadLookupTablesByUids(ltUidList, false, 1033);
                    // the code value gets us to the description which we use as the webtemplatename
                    foreach (WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow rowLookupTable in dsLookupTable.LookupTableTrees)
                        if (rowLookupTable.LT_STRUCT_UID == codeValue)
                        {
                            webTemplateName = rowLookupTable.LT_VALUE_DESC;
                        }
                    dsWssServersDataSet = wssInterop.ReadWssServerInfo();
                    dsWssSettingsDataSet = wssInterop.ReadWssSettings();
    
                    Guid wssServerUid = dsWssSettingsDataSet.WssAdmin[0].WADMIN_CURRENT_STS_SERVER_UID;
                    string wssWebFullUrl = dsWssServersDataSet.WssServers.FindByWSTS_SERVER_UID(wssServerUid).WSS_SERVER_URL
                        + "/" + dsWssSettingsDataSet.WssAdmin[0].WADMIN_DEFAULT_SITE_COLLECTION
                        + "/" + e.ProjectName.ToString();
    
                    int webTemplateLcid = 1033;
                    // If site exists then we cannot create it for this project...
                    bool SiteExists = wssInterop.WSSWebExists(wssWebFullUrl);
                    if (!SiteExists)
                    {
                        //NotYet means we will create it later
                        if (webTemplateName != "NotYet")
                        {   
                            // This is where we create a site if we need one
                            wssInterop.CreateWssSite(projectGuid, wssServerUid, wssWebFullUrl, webTemplateLcid, webTemplateName);
                            
                            string msg = string.Format("{0}: Site {1} created for Project {2}", eventName, wssWebFullUrl, e.ProjectName);
                            WriteEvent(msg, EventLogEntryType.SuccessAudit , EVENTID);
                        }
    
                        else
                        {
                            string msg = string.Format("{0}: No site required at this time for project {1}", eventName, e.ProjectName);
                            WriteEvent(msg, EventLogEntryType.Information, EVENTID);
                        }
    
    
                    }
                    else
                    {
                        string msg = string.Format("{0}: Site {1) already exists", eventName, wssWebFullUrl);
                        WriteEvent(msg, EventLogEntryType.Error, EVENTID);
                    }
    
                }
                else
                {
                    string msg = string.Format("{0}: Workspace already created", eventName);
                    WriteEvent(msg, EventLogEntryType.Information, EVENTID);
                }
                
            }
            
        private void WriteEvent(string msg, EventLogEntryType logEntryType, int eventId)
            {
                EventLog myLog = new EventLog();
                myLog.Source = "Project Event Handler";
    
                string message = msg;
                myLog.WriteEntry(msg, logEntryType, eventId);
            }
        }
    }

    Enjoy!

  • Brian Smith's Microsoft Project Support Blog

    Project at TechEd 2008 Developer – 3rd to the 6th June 2008

    • 0 Comments

    Boris Scholl, Christophe Fiessinger and I will be attending TechEd Developers 2008 in Orlando next week.

    We’ll be delivering the following sessions:

    Customizing the Microsoft Office Project Web Access User Interface

    Line-of-Business Integration Using Microsoft Office SharePoint Server 2007 and Microsoft Office Project Server 2007

    Code Samples

    Project Initiation Using Microsoft Office InfoPath and Windows Workflow Foundation

    Code Samples

    We’ll be around all week attending the Blogger Connect (next to TechEd online / Fishbowl areas) as well as the Office Developer booths.

    Don’t be shy, come and meet us we want to hear your Project Server feedback from a developer perspective!

    There may soon be pictures of Christophe and I showing up on the blog list for Project at http://technet.microsoft.com/en-us/office/projectserver/cc511254.aspx –  you have been warned!

  • 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 75 of 97 (483 items) «7374757677»