At a customer we’ve set up an Infrastructure-As-Code solution with Azure Resource Manager (ARM) for deploying Azure Resources through a Azure DevOps build and release pipelines.

To use LinkedTemplates in ARM you need to provide a URI to the template in the calling template:

 "templateLink": { 
 	"uri":"https://mystorageaccount.blob.core.windows.net/AzureTemplates/newStorageAccount.json",           
    "contentVersion":"1.0.0.0"        
   }

This needs to be a location where Azure Resource Manager can download the template, without authentication. But what if you don’t want to make your LinkedTemplates available for anyone? You can store them in a non public storage container, but use a SAS token. If you pass the URL plus SAS token to the calling template. It can use that to download the template. More about that here.

We split all resource components up into separate templates, e.g. the ipSecurityRestrictions part for a Web App is in a separate template, which need to be called from the generic web app template we’ve created. I don’t want to have to create parameters for the SAS token into each Linked Template I want to call.

So I decided to use a different solution for creating the URI:

"variables": {  
 "ipWhitelistingTemplateUrl": "[replace(deployment().properties.templateLink.uri, '/WebApp-Generic.json?', '/WebApp-IpWhitelisting.json?')]"
}

It’s not complicated and you can discuss if this is indeed a better solution. But I think it demonstrates the different possibilities in ARM templates.

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