Peter Gerritsen’s blog

For a project I needed to move variables from a project in Octopus to a library variable set for reuse in other project. Because the source project contained over 100+ variables with different scoping, I didn’t want to do this by hand.

Enter the Octopus API:

namespace OctopusVariableCopier
{
    class Program
    {
        static void Main(string[] args)
        {
            var octopusUrl = "https://<enter server url>";
            var apiKey = "API-<enter API key";

            var octopusServer = new Octopus.Client.OctopusServerEndpoint(octopusUrl, apiKey);
            var repo = new Octopus.Client.OctopusRepository(octopusServer);

            var libraryVariableSetLib = repo.LibraryVariableSets.Get("LibraryVariableSets-61");
            var libraryVariableSet = repo.VariableSets.Get(libraryVariableSetLib.VariableSetId);
            var project = repo.Projects.Get("source-project-slug");
            
            var projectVariableSet = repo.VariableSets.Get(project.VariableSetId);
            foreach (var projectVariable in projectVariableSet.Variables) {
                if (projectVariable.Scope.ToString().Contains("Action =")) // skip step scoped variables 
                    continue;

                Console.WriteLine("{0} : {1}", projectVariable.Name, projectVariable.Value);

                libraryVariableSet.Variables.Add(projectVariable);                
            }

            repo.VariableSets.Modify(libraryVariableSet);   
            
            Console.ReadLine();
        }
    }
}

After this you can use the same structure to loop through the library variables to delete the variables from the project variable set.

If later you need to check if the values from the source project are the same you can use the following check.

var projectVariable = projectVariableSet.Variables.FirstOrDefault(x => x.Name == libraryVariable.Name && x.Scope.ToString() == libraryVariable.Scope.ToString());

if (projectVariable.Value != libraryVariable.Value)
	Console.WriteLine($"Variable: {projectVariable.Name} - Scope: {projectVariable.Scope} - Project value: {projectVariable.Value} - Library value: {libraryVariable.Value}"); 

For a project we needed to determine the first upcoming date of items based on a start date and an interval in a SQL query. So the interval would be a number of years, months, weeks or days.

For this, I decided to create a scalar function that would calculate the next upcoming date.

We can offcourse use the DATEADD function, but we need the correct amount of years, months etc. to add to the startdate.

This will be the interval times the difference in years, months etc. divided by the interval. We need to add this one more time if the date for this year is already passed.

-- =============================================
-- Author:          <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:     <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnGetNextDateFromDateAndInterval]
(
       -- Add the parameters for the function here
       @firstDate DATETIME,
       @numberOfYears INT,
       @numberOfMonths INT,
       @numberOfWeeks INT,
       @numberOfDays INT
)
RETURNS DATETIME
AS
BEGIN
       IF @numberOfYears IS NOT NULL
       BEGIN
             RETURN DATEADD(YEAR, @numberOfYears * ((DATEDIFF(YEAR, @firstDate, GETDATE()) / @numberOfYears) + CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(@firstDate), DAY(@firstDate)) > GETDATE() THEN 0 ELSE 1 END), @firstDate)
       END
 
       IF @numberOfMonths IS NOT NULL
       BEGIN
             RETURN DATEADD(MONTH, @numberOfMonths * ((DATEDIFF(MONTH, @firstDate, GETDATE()) / @numberOfMonths) + CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(@firstDate), DAY(@firstDate)) > GETDATE() THEN 0 ELSE 1 END), @firstDate)
       END
 
       IF @numberOfWeeks IS NOT NULL
       BEGIN
             RETURN DATEADD(WEEK, @numberOfWeeks * ((DATEDIFF(WEEK, @firstDate, GETDATE()) / @numberOfWeeks) + CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(@firstDate), DAY(@firstDate)) > GETDATE() THEN 0 ELSE 1 END), @firstDate)
       END
 
       IF @numberOfDays IS NOT NULL
       BEGIN
             RETURN DATEADD(DAY, @numberOfDays * ((DATEDIFF(WEEK, @firstDate, GETDATE()) / @numberOfDays) + CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(@firstDate), DAY(@firstDate)) > GETDATE() THEN 0 ELSE 1 END), @firstDate)
       END
 
       RETURN GETDATE()
END
 
GO

To make sure this function calculates the correct date, I decided to create some unit tests. I like to use Dapper to perform any DB queries against SQL server.

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlClient;
using Dapper;
using System.Data;
using System.Linq;

namespace TestDbFunctionsCSharp
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestJaren()
        {
            using (SqlConnection conn = new SqlConnection("CONNSTRING_HERE"))
            {
                conn.Open();

                var refDate = DateTime.Today.AddDays(2);

                // Jaren
                Assert.AreEqual(refDate, GetNextDate(conn, refDate.AddYears(9 * -3), 3));               
            }
        }

        [TestMethod]
        public void TestMaanden()
        {
            using (SqlConnection conn = new SqlConnection("CONNSTRING_HERE"))
            {
                conn.Open();

                Assert.AreEqual(DateTime.Today.AddMonths(4), GetNextDate(conn, DateTime.Today.AddMonths(-8), numberOfMonths: 6));

                Assert.AreNotEqual(DateTime.Today, GetNextDate(conn, DateTime.Today.AddMonths(-7), numberOfMonths: 1));

                // Weken
                //Assert.AreEqual(DateTime.Today, GetNextDate(conn, DateTime.Today.AddDays(-7 * 8), numberOfWeeks: 2));
            }
        }

        [TestMethod]
        public void TestWeken()
        {
            using (SqlConnection conn = new SqlConnection("CONNSTRING_HERE"))
            {
                conn.Open();

                Assert.AreEqual(DateTime.Today.AddDays(14), GetNextDate(conn, DateTime.Today.AddDays(-7 * 8), numberOfWeeks: 2));

                Assert.AreEqual(DateTime.Today.AddDays(14), GetNextDate(conn, DateTime.Today.AddDays(-7 * 80), numberOfWeeks: 2));

                Assert.AreEqual(DateTime.Today.AddDays(7), GetNextDate(conn, DateTime.Today.AddDays(-7 * 79), numberOfWeeks: 2));
            }
        }

        private DateTime GetNextDate(SqlConnection conn, DateTime firstDate, int? numberOfYears = null, int? numberOfMonths = null, int? numberOfWeeks = null, int? numberOfDays = null)
        {
            var retval = conn
                    .Query<DateTime>(
                        "SELECT dbo.fnGetNextDateFromDateAndInterval(@firstDate, @numberOfYears, @numberOfMonths, @numberOfWeeks, @numberOfDays) AS VolgendeDatum",
                        new
                        {
                            firstDate,
                            numberOfYears,
                            numberOfMonths,
                            numberOfWeeks,
                            numberOfDays
                        },
                        commandType: CommandType.Text
                    ).First();

            return retval;
        }
    }
}

I was trying to move some Azure SQL databases to another subscription and had issues doing a publish of a SSDT project and also performing a schema compare from Visual Studio 2013. When looking in the error list I saw it had to do with timeouts on the connection.

Updating the connection timeout in the connection properties didn’t resolve the problem.

After googling / binging some more I discovered a StackOverflow post.

Appearantly using a Basic Tier will give you these problems. After scaling up to Standard the issues were resolved. I have yet to find out if you can switch back to Basic after an initial setup of the DB and from there small incremental changes don’t give issues.

After installing a languagepack help contents should be available when you create a site collection in that language. Sometimes however SharePoint gives you a message that the files have not been installed when you click the help button or go to the Help-settings page for your sitecollection.

You can force SharePoint to install the files using the hcinstal.exe tool in the bin folder under the SharePoint root (c:\program files\common files\microsoft shared\web server extensions\14\bin):

hcinstal.exe /act InstallAllHCs /loc 1043

This command will install all available help content for LCID 1043 (Dutch)

After this running this command (which can take up to 10-15 minutes easily) you need to run the SharePoint Products and Configuration Wizard on each front-end server.

I think you can also use the following 2 PowerShell commands, but I wasn’t able to test these after the first solution worked:

• Install-SPHelpCollection
• Install-SPApplicationContent

When you use the out-of-the box components for searching for people, a generic search with a keyword that is included in an added Profile property will not give the results you might expect.

This is especially a problem with the out-of-the-box webpart that shows information about a person:

When you click on one of the values in this webpart, you will be redirected to the people search results page with the value you clicked passed in as the search keyword:

But this will not give you any results. This post by kgreame outlines the same problems and some of the steps he tried to solve this issue. In the comments to that post, a workaround is mentioned: map the matching crawled properties to the ContentsHidden Managed Property.

There is however an other way. In this post by Larry Kuhn, he explains how the DEFAULTPROPERTIES within the SharePoint Search work. By setting a weight of a Managed Property, you will include it in the ranking model SharePoint uses and the property is therefore added to the default properties that are searched. SharePoint 2010 introduces the concept of Ranking Models, so the solution mentioned in that blogpost doesn’t work. We can however create our own Ranking Model. I’ve copied the information from the Ranking Model that SharePoint uses for People Search and added my own Managed Property, Expertise, with a weight of 1.0:

<?xml version="1.0" encoding="utf-8"?>
<rankingModel name="CustomPeopleRanking" id="5EA2750C-8165-4f65-BD12-6E6DAAD45FE0" description="Custom People Ranking" xmlns="http://schemas.microsoft.com/office/2009/rankingModel">
  <queryDependentFeatures>
    <queryDependentFeature pid="177" name="RankingWeightName" weight="0.5" lengthNormalization="0" />
    <queryDependentFeature pid="19" name="PreferredName" weight="1.0" lengthNormalization="0" />
    <queryDependentFeature pid="24" name="JobTitle" weight="2.0" lengthNormalization="0" />
    <queryDependentFeature pid="39" name="Responsibilities" weight="1.0" lengthNormalization="5" />
    <queryDependentFeature pid="179" name="RankingWeightLow" weight="0.2" lengthNormalization="5" />
    <queryDependentFeature pid="175" name="ContentsHidden" weight="0.1" lengthNormalization="5" />
    <queryDependentFeature pid="35" name="Memberships" weight="0.25" lengthNormalization="5" />
    <queryDependentFeature pid="178" name="RankingWeightHigh" weight="2.0" lengthNormalization="0" />
    <queryDependentFeature pid="180" name="Pronunciations" weight="0.05" lengthNormalization="0" />
    <queryDependentFeature pid="408" name="Expertise" weight="1.0" lengthNormalization="0" />
  </queryDependentFeatures>
</rankingModel>

We can then add this Ranking Model to SharePoint with Powershell:

Get-SPEnterpriseSearchServiceApplication | New-SPEnterpriseSearchRankingModel

And paste in the XML of this Ranking Model when Powershell prompts you for it.

But how do we force SharePoint to use this Ranking Model? I’ve outlined one of the solutions in a previous post.

So after adding a web part that sets the ranking model we can perform the search again:

And bingo, the user with this value in an extra profile property shows up in the search results.

Why use this over mapping the properties in the ‘ContentsHidden’ Managed Property? As you can see the ‘ContentsHidden’ property is included in the Ranking Model with a value of 0.1. If you want to give more weight to a custom property or you have more properties to which you want to assign a different weight, you will need to modify the Ranking Model.