PowerShell Snippet: Trimming The String!


When building up urls from different parameters in something like TeamCity, or Octopus, it’s simple enough to get double “//” in urls if the parameters are not consistent. So little helper functions are always useful to have imported to manage such things. Below is an example of such a thing!

function Update-Parameter
[bool] $trimstart = $false,
[bool] $trimend = $false

if ($trimstart)
$StringChecker = $param.StartsWith($character)
if ($StringChecker -eq "True")
$param = $param.TrimStart($character)
if ($trimend)
$StringChecker = $param.EndsWith($character)
if ($StringChecker -eq "True")
$param = $param.TrimEnd($character)
return $param

$url = "https://academy.microsoft.com/"
$lan = "/en-us/"
$ext = "/professional-program/data-science/"
$trim = "/"

$output = "Before Trim {0}/{1}/{2}" -f $url, $lan, $ext

$url = Update-parameter $url $trim $false $true
$lan = Update-parameter $lan $trim $true $true
$ext = Update-Parameter $ext $trim $false $true

$output = "After Trim {0}/{1}/{2}" -f $url, $lan, $ext

SqlServer PowerShell Modules NuGet Package Now Available


Back in the July Update of SSMS 2016, a bunch of new SQL PowerShell functions were added, plus two neat additions to Invoke-Sqlcmd: -outputas, which allowed you to output the result set into a data object (eg, data row, data table etc), and -ConnectionString, which allows you to pass in a connection string instead of using the pre-defined parameters. All very useful stuff, go and have a read.

However, this update has two issues: firstly, it’s not updating the classic sqlps module, but rather has created a new module: sqlserver. This new module will be regularly updated and delivered with SSMS updates. And the sqlps will only be affected by changes from the DB Engine; so Cumulative Updates, Service Packs etc. So there’s quite a bit of overlap here.

Secondly, I don’t like how this new module is being delivered via a monthly release of SSMS. That’s a 900MB download and a long install to gain access to a module that is only 2MB in size. To make matters worse, the SQL Server 2016 Feature Pack, which includes a SQL PowerShell msi, IS NOT being updated! Waah!

So this may just seem like a whinge, but I have checked and it is possible to install this module on it’s own and use without having anything else other than a couple of prerequisites installed. So I’m going to be pro-active: I have taken in upon myself to upload the module to NuGet for people to consume. As far as prerequisites go, you only need SMO and SQLCLR installed, available through the Feature Packs, though they do have to be the SQL Server 2016 versions of these objects.

As mentioned in one of the links above, you have to Remove-Module sqlps first before you import the new module.

I’ll update the NuGet package every time there’s a new release of SSMS.

Oh, and here’s a quick syntax of how to import the module:

Import-Module -Name "C:\nuget\PowerShell.SqlServer.Modules.1.0.1\sqlserver" -Verbose
$conn = ".;Integrated Security=True"
Invoke-Sqlcmd -ConnectionString $conn -query "SELECT GETDATE() AS TimeOfQuery;"

Team City Meta Runner: Get Build Number

One of the neat things about TeamCity is that it gives you a great deal of control over your process. TeamCity is great to use as a control flow for your pipeline, especially if you are using Octopus to deploy. But one of the pain points here is keeping the parity between build/deploy versions in Octopus the same as in TeamCity. It’s important because it prevents users from having to jump between the UI’s to keep track of what is deployed where, especially if you are automating the whole process.

There’s several ways to keep the build numbers aligned, the most obvious being snapshot dependencies. However, snapshot dependencies can open up a world of pain: if builds are altered then a snapshot may become invalid when it’s not intended, so a build may trigger other builds being launched unintentionally.

Fortunately, there is a method to share build numbers amongst other builds without setting up snapshot dependencies. By using a meta runner, you can use the TeamCity rest api to grab a build number of a given build.

“What is a meta runner?” I hear some of you say…. to quote the TeamCity wiki: Continue reading “Team City Meta Runner: Get Build Number”

PowerShell Snippet: Verify Minimum Version of .NET

Occasionally it’s useful to verify the minimum version of .NET installed on a box. This PowerShell does exactly that.

$Netver = (Get-ItemProperty ‘HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full’ -Name Release).Release
write-host ".NET Version : $netver"

ElseIf ($Netver -le 394271)
throw "At least .NET Framework 4.6.1 needs to be installed on this tentacle to use Microsoft.Data.Tools.Msbuild."

PowerShell Snippet #7: Retrieve SQL Server Licence Key

Copied from somewhere else on the internet, this PowerShell script will return the product key used for a SQL instance Install. Super useful when changing licenses on temporary VM’s I spin up and play around with to SQL Developer whose instances have passed the Enterprise evaluation use-by date. Putting this here for my own benefit. I claim no kudos!


function GetSqlServerProductKey {
## function to retrieve the license key of a SQL 2014 Server.
param ($targets = ".")
$hklm = 2147483650
$regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\Setup"
$regValue1 = "DigitalProductId"
$regValue2 = "PatchLevel"
$regValue3 = "Edition"
Foreach ($target in $targets) {
$productKey = $null
$win32os = $null
$wmi = [WMIClass]"\\$target\root\default:stdRegProv"
$data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)
[string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
[string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue
$binArray = ($data.uValue)[0..16]
$charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
## decrypt base24 encoded binary data
For ($i = 24; $i -ge 0; $i--) {
$k = 0
For ($j = 14; $j -ge 0; $j--) {
$k = $k * 256 -bxor $binArray[$j]
$binArray[$j] = [math]::truncate($k / 24)
$k = $k % 24
$productKey = $charsArray[$k] + $productKey
If (($i % 5 -eq 0) -and ($i -ne 0)) {
$productKey = "-" + $productKey
$win32os = Get-WmiObject Win32_OperatingSystem -computer $target
$obj = New-Object Object
$obj | Add-Member Noteproperty Computer -value $target
$obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
$obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
$obj | Add-Member Noteproperty SQLver -value $SQLver
$obj | Add-Member Noteproperty SQLedition -value $SQLedition
$obj | Add-Member Noteproperty ProductKey -value $productkey


PowerShell Snippet #6

Recently I needed to run Invoke-Sqlcmd that would use a connection string of a variety of parameters. Below is the script I used to accept whatever parameters would be passed in to the “Invoke-sqlcmd”. The input file contained the same text as the $query parameter.


$ServerInstance = ".\instance"
$database =  "Northgale"
$uname = "user"
$pword = "password123"
$query = "'SELECT GETDATE() AS TimeOfQuery;'"
$inputFile = "C:\Users\Richard\Documents\inputfile.sql"

$connectionString = "-ServerInstance $ServerInstance -UserName $uname -Password $pword -Database $database -verbose"
$connectionString  = $connectionString -split '(?=prg=PowerShell-)'
$InvokeSqlcmd  = "Invoke-Sqlcmd -inputfile $inputFile $connectionstring"
write-host $InvokeSqlcmd -verbose
Powershell.exe -command "$invokeSqlCmd"

VSTS Hosted Build Agent Specs

I was interested to know just what the hardware specifications of the hosted build agent is. So I added some PowerShell to read out the info below:


2016-06-29T09:23:31.3935358Z systemname      Name                                      DeviceID NumberOfCores NumberOfLogicalProcessors Addresswidth
2016-06-29T09:23:31.3935358Z ----------      ----                                      -------- ------------- ------------------------- ------------
2016-06-29T09:23:31.3935358Z TASKAGENT5-0010 Intel(R) Xeon(R) CPU E5-2673 v3 @ 2.40GHz CPU0                 2                         2           64
2016-06-29T09:23:31.4095356Z Total memory:  7167.55078125

What piqued my interest greater was that this is the exact same spec for a D2 v2 box that is available via Azure. Clearly, Microsoft have a build agent template which is built, stored in a pool, and provisioned whenever a build takes place. One must assume that it is disposed of after a single use to prevent and nefarious activity by other people trying to steal code….


Deploying SSDT Scripts (With My Good Eye Closed)

(If anyone actually gets the reference: kudos. If not, then it’s just that you’re not a Soundgarden fan)

So, there’s something that has been troubling me about deploying to databases with SSDT for some time. Actually, it’s not a SSDT-exclusive problem per se, it’s more of a database/release management process.

What it pertains to is that most people like to create the deployment script for review prior to deploying to production. This makes sense if you are new to the Continuous Deployment process, and that maybe sometimes SSDT creates scripts that are not quite expected. You may skip this because you feel rather more confident in your deployments, or the database sufficiently simple enough to just deploy without having to review. But generating the script as a deploy artefact is still a good idea, because auditing.

At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?

The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.
Continue reading “Deploying SSDT Scripts (With My Good Eye Closed)”

PowerShell Snippet#5: Running MSBuild

Script on running MSBuild via PowerShell. This ties up neatly with my previous post.

$msbuild = "C:\Windows\Microsoft.Net\Framework\v4.0.30319\MSBuild.exe"
$MsBuilExists = Test-Path $msbuild
If ($MsBuilExists -ne $true) {write-host "msbuild does not exist at this location. Install Visual Studio 2015 (Community Edition should be adequate)"}
$buildFile = $PSScriptRoot+"\BuildAllDBProjects.targets.xml"
& $msbuild $buildFile

MSBuild Snippet#1 : Ignore Solutions

How do we ignore solutions in a build? We could specify only the ones we want to build, but depending on your weighting of include/exclude solutions, this could be costly. So here’s how to ignore solutions using MSBuild:


<Project ToolsVersion="4.0"

<ProjectReference Include=".\**\AV2014.sln" Exclude=".\**\DBSolutionToIgnore.sln"/>

<Target Name="Default">
<MSBuild Projects="@(ProjectReference)" Targets="Rebuild"/>