mandag den 28. juli 2014

Project Server - PSI - Update enterprise custom field with lookuptable

In Project Server - PSI it should be very fast to assign a new value to a custom field with a lookuptable. However this usually takes a lot of time for me mainly because I never can remember extactly how the field should be updated.

That is why i created this post to show how a custom field (assigned to a lookuptable) can be assigned a new value.
The biggest issue is that I can't remember how the different entities are connected together. The binding between the project and the lookuptable values are shown in the picture below.


There are a number of steps to assign a value to a custom field with a lookuptable.
 - Read the project dataset
 - Find the custom field; MD_PROP_UID and MD_LOOKUP_TABLE_UID
 - Find the ProjectCustomFieldRow with the same MD_PROP_UID
 - Find the LookupTable with the same MD_LOOKUP_TABLE_UID
 - Find the LookuptableTreesRow with the LT_VALUE_TEXT you want to assign.
 - Assign the LT_STRUCT_UID from the LookupTableTreesRow to the ProjectCustomFieldsRow's CODE_VALUE
    - If the ProjectCustomFieldsRow does not exists, create it.
 - Save and publish the project

The basic code for updating the custom field is:
#region Set CF value
WS_CF.CustomFieldDataSet cfds = customfields.ReadCustomFields("", false);
WS_Project.ProjectDataSet.ProjectCustomFieldsRow projectfield = null;
WS_CF.CustomFieldDataSet.CustomFieldsRow field = null;
//find the custom field row in the project dataset
foreach (WS_CF.CustomFieldDataSet.CustomFieldsRow cfrow in cfds.CustomFields)
{
if (cfrow.MD_PROP_NAME == customfieldname)
{
    field = cfrow;
    foreach (WS_Project.ProjectDataSet.ProjectCustomFieldsRow pcf in projectDS.ProjectCustomFields)
    {
        if (pcf.MD_PROP_UID == cfrow.MD_PROP_UID)
        {
            projectfield = pcf;
            break;
        }
    }
}
}
if (field.IsMD_LOOKUP_TABLE_UIDNull() == false && field.MD_LOOKUP_TABLE_UID != Guid.Empty)
{
Guid ltrowuid = Guid.Empty;
#region find LT Code Value
WS_LT.LookupTableDataSet ltds = null;

ltds = lookuptable.ReadLookupTablesByUids(new Guid[] { field.MD_LOOKUP_TABLE_UID }, false, 1033);


decimal newSortIndx = 1;
foreach (WS_LT.LookupTableDataSet.LookupTableTreesRow valueRow in ltds.LookupTableTrees)
{
    if (valueRow.LT_VALUE_SORT_INDEX >= newSortIndx)
    {
        newSortIndx = valueRow.LT_VALUE_SORT_INDEX + 1;
    }
    if (valueRow.LT_VALUE_FULL == (string)customfieldvalue || valueRow.LT_VALUE_TEXT == (string)customfieldvalue)
    {
        ltrowuid = valueRow.LT_STRUCT_UID;
    }
}
#region create LT entry if it does not exists
if (ltrowuid == Guid.Empty)
{
    //Create row
    WS_LT.LookupTableDataSet.LookupTableTreesRow ltrow = ltds.LookupTableTrees.NewLookupTableTreesRow();
    ltrow.LT_UID = field.MD_LOOKUP_TABLE_UID;
    ltrow.LT_STRUCT_UID = Guid.NewGuid();
    //ltrow.LT_PARENT_STRUCT_UID = ltuid; //Top level
    ltrow.LT_VALUE_TEXT = customfieldvalue;
    ltrow.LT_VALUE_FULL = customfieldvalue;
    ltrow.LT_VALUE_SORT_INDEX = newSortIndx;
    ltds.LookupTableTrees.AddLookupTableTreesRow(ltrow);



    lookuptable.CheckOutLookupTables(new Guid[] { field.MD_LOOKUP_TABLE_UID });
    lookuptable.UpdateLookupTables(ltds, false, true, 1033);

}
#endregion
#endregion

if (projectfield == null)
{
    projectfield = projectDS.ProjectCustomFields.NewProjectCustomFieldsRow();
    projectfield.MD_PROP_UID = field.MD_PROP_UID;
    projectfield.CUSTOM_FIELD_UID = Guid.NewGuid();
    projectfield.PROJ_UID = projectDS.Project[0].PROJ_UID;
    projectDS.ProjectCustomFields.AddProjectCustomFieldsRow(projectfield);

}

projectfield.CODE_VALUE = ltrowuid;

The Complete Code for updating a custom field with a lookuptable value is provided below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SetCustomField
{
    class Program
    {
        static void Main(string[] args)
        {
            Guid projectUid = new Guid("113d1cb6-a6b6-4dc4-a55b-e3bf1f65fbb6");
            string customfieldname = "Project Status";
            string customfieldvalue = "Completed";

            #region Init
            string ProjectServerURL = "";
            string ProjectServerAccount = "";
            string ProjectServerAccountPassword = "";
            string ProjectServerDomain = "";
            WS_Project.Project wsproject = null;
            WS_Queue.QueueSystem wsqueue = null;
            WS_CF.CustomFields customfields = null;
            WS_LT.LookupTable lookuptable = null;

            ProjectServerURL = "http://ProjectServer/PWA/";
            ProjectServerAccount = "Administrator";
            ProjectServerAccountPassword = "pass@word1";
            ProjectServerDomain = "CONTOSO";

            System.Net.NetworkCredential cred = new System.Net.NetworkCredential(ProjectServerAccount, ProjectServerAccountPassword, ProjectServerDomain);


            wsproject = new WS_Project.Project();
            wsproject.Url = ProjectServerURL + "/_vti_bin/psi/project.asmx";
            wsproject.Credentials = cred;


            wsqueue = new WS_Queue.QueueSystem();
            wsqueue.Url = ProjectServerURL + "/_vti_bin/psi/queuesystem.asmx";
            wsqueue.Credentials = cred;

            customfields = new WS_CF.CustomFields();
            customfields.Url = ProjectServerURL + "/_vti_bin/psi/customfields.asmx";
            customfields.Credentials = cred;

            lookuptable = new WS_LT.LookupTable();
            lookuptable.Url = ProjectServerURL + "/_vti_bin/psi/lookuptable.asmx";
            lookuptable.Credentials = cred;
            #endregion

            #region read project
            WS_Project.ProjectDataSet projectDS = wsproject.ReadProject(projectUid, WS_Project.DataStoreEnum.WorkingStore);
            #endregion

            #region Set CF value
            WS_CF.CustomFieldDataSet cfds = customfields.ReadCustomFields("", false);
            WS_Project.ProjectDataSet.ProjectCustomFieldsRow projectfield = null;
            WS_CF.CustomFieldDataSet.CustomFieldsRow field = null;
            //find the custom field row in the project dataset
            foreach (WS_CF.CustomFieldDataSet.CustomFieldsRow cfrow in cfds.CustomFields)
            {
                if (cfrow.MD_PROP_NAME == customfieldname)
                {
                    field = cfrow;
                    foreach (WS_Project.ProjectDataSet.ProjectCustomFieldsRow pcf in projectDS.ProjectCustomFields)
                    {
                        if (pcf.MD_PROP_UID == cfrow.MD_PROP_UID)
                        {
                            projectfield = pcf;
                            break;
                        }
                    }
                }
            }
            if (field.IsMD_LOOKUP_TABLE_UIDNull() == false && field.MD_LOOKUP_TABLE_UID != Guid.Empty)
            {
                Guid ltrowuid = Guid.Empty;
                #region find LT Code Value
                WS_LT.LookupTableDataSet ltds = null;

                ltds = lookuptable.ReadLookupTablesByUids(new Guid[] { field.MD_LOOKUP_TABLE_UID }, false, 1033);


                decimal newSortIndx = 1;
                foreach (WS_LT.LookupTableDataSet.LookupTableTreesRow valueRow in ltds.LookupTableTrees)
                {
                    if (valueRow.LT_VALUE_SORT_INDEX >= newSortIndx)
                    {
                        newSortIndx = valueRow.LT_VALUE_SORT_INDEX + 1;
                    }
                    if (valueRow.LT_VALUE_FULL == (string)customfieldvalue || valueRow.LT_VALUE_TEXT == (string)customfieldvalue)
                    {
                        ltrowuid = valueRow.LT_STRUCT_UID;
                    }
                }
                #region create LT entry if it does not exists
                if (ltrowuid == Guid.Empty)
                {
                    //Create row
                    WS_LT.LookupTableDataSet.LookupTableTreesRow ltrow = ltds.LookupTableTrees.NewLookupTableTreesRow();
                    ltrow.LT_UID = field.MD_LOOKUP_TABLE_UID;
                    ltrow.LT_STRUCT_UID = Guid.NewGuid();
                    //ltrow.LT_PARENT_STRUCT_UID = ltuid; //Top level
                    ltrow.LT_VALUE_TEXT = customfieldvalue;
                    ltrow.LT_VALUE_FULL = customfieldvalue;
                    ltrow.LT_VALUE_SORT_INDEX = newSortIndx;
                    ltds.LookupTableTrees.AddLookupTableTreesRow(ltrow);



                    lookuptable.CheckOutLookupTables(new Guid[] { field.MD_LOOKUP_TABLE_UID });
                    lookuptable.UpdateLookupTables(ltds, false, true, 1033);

                }
                #endregion
                #endregion

                if (projectfield == null)
                {
                    projectfield = projectDS.ProjectCustomFields.NewProjectCustomFieldsRow();
                    projectfield.MD_PROP_UID = field.MD_PROP_UID;
                    projectfield.CUSTOM_FIELD_UID = Guid.NewGuid();
                    projectfield.PROJ_UID = projectDS.Project[0].PROJ_UID;
                    projectDS.ProjectCustomFields.AddProjectCustomFieldsRow(projectfield);

                }

                projectfield.CODE_VALUE = ltrowuid;


            }
            #endregion

            #region Update project
            Guid sessionUid = Guid.NewGuid();

            wsproject.CheckOutProject(projectUid, sessionUid, "Checkout by code");
            Guid jobid = Guid.NewGuid();
            wsproject.QueueUpdateProject(jobid, sessionUid, projectDS, false);
            WaitForQueue(wsqueue, 60 * 5, jobid);

            jobid = Guid.NewGuid();
            wsproject.QueuePublish(jobid, projectUid, true, string.Empty); //string.empty == create std workspace
            WaitForQueue(wsqueue, 60 * 5, jobid);

            jobid = Guid.NewGuid();
            wsproject.QueueCheckInProject(jobid, projectUid, false, sessionUid, "Checkin by code");
            #endregion

        }
        public static bool WaitForQueue(WS_Queue.QueueSystem q, int timeOut, Guid jobId)
        {
            int INCREMENTALSLEEPTIME = 2;

            int wait;                 // Number of seconds to wait
            decimal seconds;          // For reporting wait time in decimal
            // format
            string xmlError;          // XML error output from the queue
            string queueStatus;       // Outer XML of xmlError string
            string status = "";       // Summary status report for output
            bool firstPass = true;    // First iteration through the while 
            // statement
            int timeSlept = 0;        // Total time slept (seconds)
            bool jobIsDone = false;   // The queue job completed 
            // successfully, if true
            bool stopWait = false;    // Abort the wait, if true
            WS_Queue.JobState jobState; // Status of the queue job

            while (true)
            {
                // On the first iteration, wait the incremental sleep time 
                // or the maximum requested timeout. 
                if (firstPass)
                {
                    // Get the estimated time to wait for the queue to 
                    // process the job.
                    // The output from GetJobWaitTime is in seconds.
                    wait = q.GetJobWaitTime(jobId);

                    status = string.Format("Estimated job wait time: {0} seconds", wait);

                    if (timeOut < INCREMENTALSLEEPTIME) wait = timeOut;
                    else wait = INCREMENTALSLEEPTIME;

                    firstPass = false;
                }
                else
                {
                    // If job is not done, wait the incremental sleep time
                    wait = INCREMENTALSLEEPTIME;
                }

                System.Threading.Thread.Sleep(wait * 1000); // Milliseconds

                timeSlept += wait;

                // Check job state
                jobState = q.GetJobCompletionState(jobId, out xmlError);

                // Add the XML error output to the status
                System.IO.StringReader sr = new System.IO.StringReader(xmlError);
                using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(sr))
                {
                    reader.MoveToContent();
                    queueStatus = reader.ReadOuterXml();
                }
                // Don't add an empty <errinfo> tag
                if (queueStatus != "<errinfo />") status += "\n\n" + queueStatus;
                if (jobState == WS_Queue.JobState.Success)
                {
                    jobIsDone = true;
                }
                else if (jobState == WS_Queue.JobState.Unknown
                    || jobState == WS_Queue.JobState.Failed
                    || jobState == WS_Queue.JobState.FailedNotBlocking
                    || jobState == WS_Queue.JobState.CorrelationBlocked
                    || jobState == WS_Queue.JobState.Canceled)
                {
                    stopWait = true;
                }

                if (!jobIsDone && timeSlept >= timeOut)
                {
                    // Cancel the job, otherwise the queue keeps processing 
                    // until the job is complete.
                    //q.CancelJobSimple(jobId);
                    stopWait = true;
                    status += string.Format("\n\nExceeded timeout of {0} seconds", timeOut);
                }

                if (jobIsDone || stopWait)
                {
                    seconds = Convert.ToDecimal(timeSlept);
                    status += string.Format(
                        "\n\nJobState: {0:G}\n\nTotal time slept: {1:N} seconds",
                        jobState, seconds);
                    break;
                }
            }
            //statusOut = status;
            return jobIsDone;
        }
    }
}

       

søndag den 27. juli 2014

SharePoint/Project Server - Firewall port open list

Firewall port open list

Every time I have to install a new instance of Project Server I forget which ports have to be open.
The ports are divided into 3 lists, one list for the web front end servers, one list for the application server and one list for the SQL server.
These ports are based on a 2013 installation of SharePoint/Project Server. the 2010 installation defer a little bit.


Web Frontend Server

When a range is specified all ports between the range must be opened.
     Port(s)           Protocol        Bound Description
-       80                    TCP                  In         http
-       443                  TCP                  In         https/ssl
-        25                    TCP                  In         SMTP for e-mail integration
-        16500-16519      TCP                  In         Ports used by the search index component
-       22233-22236    TCP                  In/Out  Ports required for the AppFabric Caching Service
-        32843-32845    TCP                  In         Communication between Web servers and service applications
-       32846               TCP                  In/Out  SharePoint User Code Service
-       808-809            TCP                  In         Office Web Apps
-        5725                 TCP                  In         User Profile Synchronization Service
-        389                   TCP+UDP         In         User Profile Synchronization Service (LDAP Service)
-        88                    TCP+UDP         In         User Profile Synchronization Service (Kerberos)
-       53                    TCP+UDP         In/Out  User Profile Synchronization Service (DNS)
-        3000                 TCP                  In/Out  Projectum Resource Manager
-        1433                 TCP                  Out      SQL Server default communication port (if no alias or custom port)
-        1434                 UDP                 Out      SQL Server default port used to establish connection (if no alias or custom port)
-        445                  TCP                  Out      SQL Server over named pipes
-        2383                 TCP                  Out      SQL Analysis Server default communication port (if no alias or custom port)


Application Server

When a range is specified all ports between the range must be opened.
     Port(s)           Protocol        Bound Description
-       80                    TCP                  In         http
-       443                  TCP                  In         https/ssl
-        25                    TCP                  In         SMTP for e-mail integration
-        16500-16519      TCP                  In         Ports used by the search index component
-       22233-22236    TCP                  In/Out  Ports required for the AppFabric Caching Service
-        32843-32845    TCP                  In         Communication between Web servers and service applications
-       32846               TCP                  In/Out  SharePoint User Code Service
-       808-809            TCP                  In         Office Web Apps
-        5725                 TCP                  In         User Profile Synchronization Service
-        389                   TCP+UDP         In         User Profile Synchronization Service (LDAP Service)
-        88                    TCP+UDP         In         User Profile Synchronization Service (Kerberos)
-       53                    TCP+UDP         In/Out  User Profile Synchronization Service (DNS)
-        3000                 TCP                  In/Out  Projectum Resource Manager
-        1433                 TCP                  Out      SQL Server default communication port (if no alias or custom port)
-        1434                 UDP                 Out      SQL Server default port used to establish connection (if no alias or custom port)
-        445                  TCP                  Out      SQL Server over named pipes
-        2383                 TCP                  Out      SQL Analysis Server default communication port (if no alias or custom port)

 

SQL Server

When a range is specified all ports between the range must be opened.
     Port(s)           Protocol        Bound Description
-        1433                 TCP                  In         SQL Server default communication port (if no alias or custom port)
-        1434                 UDP                 In         SQL Server default port used to establish connection (if no alias or custom port)
-        445                  TCP                  In         SQL Server over named pipes
-        2383                TCP                  In         SQL Analysis Server default communication port (if no alias or custom port)

tirsdag den 22. juli 2014

How to move web front end server in SharePoint (2010/2013)

How to move web front end server in SharePoint


When i install a new SharePoint farm i often make the mistake to run the configuration wizard on the application server first. this results in the Central Administration and all web applications to be hosted on the application server instead of the web front end server.
follow this guide to solve the issue.

The solution consists of 4 steps.
1. Unprovision the Central Admin from the application server
2. Provision the Central Admin to the web front end server
3. Update alternative access mappings

And in more detail:

1. Unprovision the Central Admin from the application server

Log in to application server and start the SharePoint Configuration wizard.
Click Next/Yes and make sure you do not desconnect from the farm.
 

Click Yes to remove the Central Admin and web sites from the machine.

If you do not see the above screen click "Advance Settings" on the summary page.
 
Run the wizard.

2. Provision the Central Admin to the web front end server

Log in to web front end server and start the SharePoint Configuration wizard.
Click Next/Yes and make sure you do not desconnect from the farm.
Click Next/Yes until you are at the summary page, Click the Advance Settings button.


On the Advance Settings page choose to host the web site.
Run the wizard.

3. Update alternative access mappings

The alternative access mappings are not updated by default, you have to do this manually.
Go to Central Administration and click on Alternative Access Mappings.

Update the mappings here to the front end server.