All posts by knarfalingus

Ssms 18.6 – exiting on start

So recently, SSMS started exiting on startup for me. It would display the splash screen, and then about the time the connection dialog was to show, it would just exit. Initially I tried repair, then re-installing, erasing its section in the registry, and the files in AppData/Local, nothing worked.

I checked the Windows Event log and found the following exception:

Application: Ssms.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.ArgumentNullException
   at System.Windows.Forms.ComboBox+ObjectCollection.AddInternal(System.Object)
   at System.Windows.Forms.ComboBox+ObjectCollection.Add(System.Object)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.UpdateServerInstanceMruOnly()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.UpdateServerInstanceMruAndValue()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.OnSTIndexChange(System.Object, System.EventArgs)
   at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(System.EventArgs)
   at System.Windows.Forms.ComboBox.set_SelectedIndex(Int32)
   at System.Windows.Forms.ComboBox.set_SelectedItem(System.Object)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.LoginControl.InitFromLastUsedServerType()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.ConnectionDialog.DoOneTimeInitialization()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.ConnectionDialog.OnVisibleChanged(System.EventArgs)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ShellConnectionDialog.OnVisibleChanged(System.EventArgs)
   at System.Windows.Forms.Control.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.ScrollableControl.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WmShowWindow(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Form.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.OnMessage(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.Control+ControlNativeWindow.WndProc(System.Windows.Forms.Message ByRef)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr, Int32, IntPtr, IntPtr)

This made me think, the error appears to be on loading a combo box, perhaps the combo that has the connections. So where are the list of connections stored? After some googling I found this stack overflow answer Where does SSMS store its server names

So for 18.x, this is in C:\Users\[USERNAME]\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml

I renamed the file to UserSettings.xml.old, started SSMS and voila!, it created a new user settings file and I was good to go

Running both XML files through Beyond Compare, I found this section which I believe to be the most likely the culprit:


ClickOnce, Windows Task Scheduler and Windows Server 2016/2019

Today I encountered a click once app from a third party partner that would not run on an existing Windows 2016 server. It was being shifted off of a Windows 2008 R2 server that was headed to oblivion. The task on the new server would start but never complete.

The task, which was created by the third party program, basically did something like this




On the old server, IE would actually pop-up and be visible if I was logged in, leading me to believe this was an interactive services issue. After a bit of head scratching, the solution turned out to be to call into click once directly via the “ClickOnce Application Deployment Support Library” and bypass Internet Explorer 11 completely like so :

rundll32.exe dfshim.dll,ShOpenVerbApplication


References :

IIS APPPOOL : Some or all identity references could not be translated…

System.Security.Principal.SecurityIdentifier.Translate : “Some or all identity references could not be translated”

icalcs : “No mapping between account names and security IDs was done”

So I was trying to add a web application via powershell and got stuck on the first message when trying to grant the web pool folder access, I tried various items, and then tried icalcs from the command line and got the second message. Turns out you need to force IIS to commit the changes to create the web pool! The real issue is it didn’t exist yet.

# add a web application

# reset for clean slate
Reset-IISServerManager -Confirm:$false


$siteName = "Default Web Site"
$appName = "MyBlog"
$appPoolName = "MyBlogAppPool"
$appFolder = "C:\inetpub\wwwroot\MyBlogApp"

# add the app pool
$server = Get-IISServerManager
$appPool = $server.ApplicationPools.Add($appPoolName)
# list props
# $appPool | select-object *
$appPool.ManagedRuntimeVersion = "v4.0"

# IMPORTANT that this is commited, otherwise ACL below will fail 
# none of the error messages will explain the app pool doesn't exist


# verify it exists

Get-ChildItem -Path IIS:\AppPools

# add the folder

New-Item -ItemType "directory" $appFolder

# set permissions on folder

$server = Get-IISServerManager
$appPoolSid = $server.ApplicationPools["$appPoolName"].Attributes['applicationPoolSid']
$identifier = New-Object System.Security.Principal.SecurityIdentifier $appPoolSid.Value
$user = $identifier.Translate([System.Security.Principal.NTAccount])

$acl = Get-Acl $appFolder
#$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user,"FullControl", "Allow")
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule($user,"FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
Set-Acl $appFolder $Acl  

# create the web app
Reset-IISServerManager -Confirm:$false

New-WebApplication -Name $appName -Site $siteName -PhysicalPath $appFolder -ApplicationPool $appPoolName



Powershell & Disk Setup

So recently I had to setup some drives but wanted to bypass the traditonal compmgmt route (or diskmgmt).  Powershell proved to be much faster than the old way, it only takes a few seconds to setup an SSD (longer if you want to format).

#which drives are partition style raw?


# so in the above example I noted disk number 4 is RAW, I wanted it to be the N drive
# allocate all space, format as GPT and label it "N-DRIVE"

Get-Disk | Where-object Number -eq 4 | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -DriveLetter N -UseMaximumSize | Format-Volume -FileSystem NTFS -NewFileSystemLabel "N-DRIVE" -Confirm:$false




Powershell & Hyper-V

One thing I’ve only come to appreciate lately is how quick it can be to do things in powershell vs. toiling with virtmgmt ui, let alone fussing with the firewall. Here is a quick script for adding an existing VHDX

# existing VHDX located at D:\myvm\myvm.vhdx
# 4GB RAM, 1 proc, use switch called GuestVirtualSwitch
# secure boot off (Linux, possible to have On with effort )

$vmname="MYVM"; $procs=1; $vhdpath="T:\MYVM\MYVM.vhdx"; $mem=4GB; $disksize = 128GB; $secureboot="On"
$iso = "b:\software\linux_os_install.iso" 

New-VHD -Path $vhdpath -SizeBytes $disksize
New-VM -Name $vmname -MemoryStartupBytes $mem -Switch GuestVirtualSwitch -BootDevice VHD -Generation 2 -VHDPath $vhdpath
Set-VMProcessor $vmname -Count $procs
Set-VM -VMName $vmname -CheckpointType Disabled
Set-VMFirmware $vmname -EnableSecureBoot $secureboot
Add-VMDvdDrive -VMName $vmname -Path $iso
$dvd = Get-VmDvdDrive -VMName $VMName
Set-VMFirmware -VMName $vmname -FirstBootDevice $dvd
Start-VM $vmname

VS Offline Installer Cleanup

I’ve been using the VS 2017 Offline installer for a while after reading about it here.  It works pretty well and I’ve dutifully run the layout command many times when VS has had a new update.  After a while though this folder layout grows to a significant size, especially since I also follow the layout approach on the VS preview.

After researching a bit I found the clean argument for Visual Studio setup, which can remove an older catalog.  The list of older catalogs are in the archive sub-folder of the layout folder.

So with a little experimentation, I came up with the Powershell script below.  This took my layout folder usage from 372 GB down to a mere 100 GB total for both VS and VS Preview combined.

# Cleanup.ps1
Function Clear-VSOfflineLayout

Get-ChildItem -Directory $layoutpath\Archive `
| Sort-Object CreationTime `
| ForEach-Object `
$fn = "$layoutpath\Archive\" + $_.Name + "\Catalog.json"
Write-Host $_.CreationTime.ToString() $fn
if (Test-Path $fn -PathType Leaf)
$arg = "--layout $layoutpath --clean " + $fn + " --passive"
Start-Process "$exepath" -ArgumentList $arg -Wait
Write-Host "Catalog already removed: " $fn

To run the script, pass the path of your layout folder and the path to vs_setup.exe to the powershell function

PS D:\layout>. .\cleanup.ps1
PS D:\layout> Clear-VSOfflineLayout -layoutpath "D:\Layout\Vs2017_preview_offline" -exepath "D:\layout\vs_professional_preview.exe"



Hyper-V encountered an error trying to access object on computer

Just repeating in one spot the two solutions for this problem, since I spent a bit of time being stymied by this on two separate occasions now.  The first thing to try from the command line is:

MOFCOMP %SYSTEMROOT%\System32\WindowsVirtualization.V2.mof

If that doesn’t work, are any of your VM’s non-startable? For example do you have an old VM you didn’t plan to use anymore so you stopped it and deleted the VHD but left the entry?  If so you may encounter the error in the title when trying to add a new VM or similar.  If so delete the entry that is no longer needed.

If that doesn’t work, drop down to powershell and run

Disable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V

This will disable/remove the Hyper V feature.  Reboot and the re-install with

Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All

The last solution was required when I switched to a new router on my network, afterward Hyper-V core showed only one NIC (there are two physical on the machine) but everything else ran fine.  Until I tried to add a new VM a few weeks later,  then I got the message at the top.   You will have to setup new virtual switches and reconfigure your VM’s to use them in this case.


Reference :




LG UD4379 control via RS232 using C#

Recently I decided to upgrade my primary home computer monitor.  It had been 10 years since I bought my last monitor, a 24″ DELL, and its predecessor which is a 19″, that served as a secondary was bought a few years before that.   I went for what is one of the larger ones currently on the market, the LG 43UD79 .  This monitor has six inputs, four HDMI, one Display Port, one USB-C.

There are also eight ways you can configure the inputs.

  • Single input uses entire monitor
  • Singe input with PIP in either upper right, upper left, lower right or lower left (4 options total). PIP has a transparency setting to boot.
  • One input top half, one bottom
  • One input left half, one right half
  • Quad mode, with an input in each corner

You also get to choose which input sends sound to the built in speakers.

Taken all together, that is a lot of possibilities.  Furthermore, you can use the joystick at the bottom of the monitor to change inputs/arrangements, or use the remote control.  Now the remote control is useful, but it is still a bit of work.  Switching from a 4 input quad mode, to single input with PIP in the upper right, audio coming from the PIP source?  Lots of keypresses on the remote.

Luckily the LG UD4379 comes with an RS232 port, and I can write code.  So long story short I wrote a small C# program to handle switching from mode to mode.  I call the program from a set of shortcuts in a folder on my desktop, each named by what they do.

Main caveats so far :

  • Sound input can’t be changed via RS232 as far as I can tell, keep the remote handy.
  • Best to have another dedicated monitor to run the program as switching away will make the program inaccessible (you can’t see it).  Wasn’t an issue for me since I made my old main monitor the new secondary, dedicated to the PC that has the program installed.
  • Can’t change PIP transparency via RS232, but at least the monitor seems to remember it.

The code and hardware used can be found here on GitHub

Quad mode

Single input with PIP





Today I was writing a stored procedure and had the need to create a dynamic PIVOT query in T-SQL and put the results in a temporary table. Not being a regular PIVOT user I figured this was a well known and solved issue.

Surprisingly, my first google hits provided no relief. Some posts I found mentioned OPENQUERY or OPENROWSET or using global temp tables. There were posts about dynamic pivots, but the temp table issue was unresolved. sp_executesql opens another session, temp tables created within it fall out of scope, you can’t create the temp table in advance because you don’t know all the columns, etc.

I thought about it for a while and realized the solution, just create the temp table with one column, and then dynamically alter it. A working example is below.

This example will be all temp tables so its easy to run, but a true real world example would have the sales and manufacturer info in persistent tables, primary keys, foreign keys, indexes etc. etc.

First lets create two tables to report on. Most PIVOT examples use some arbitrary piece of data like a name or date text as the column names. Not a fan of this myself. The source of my data will be three simple tables, #Manufacturers, #Years and #Sales

Note: Apparently the T-SQL syntax highlighter I use doesn’t like NVARCHAR(MAX), sp_executesql, or QUOTENAME(), hence the lame coloring for now.

First Manufacturers:

CREATE TABLE #Manufacturers
	ManufacturerID INT PRIMARY KEY,
	Name VARCHAR(128)
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (1,'Dell')
INSERT INTO #Manufacturers (ManufacturerID, Name)
VALUES (2,'Lenovo')
INSERT INTO #Manufacturers (ManufacturerID, Name)

Next #Years. Now Year is actually one of those names that actually works ok as a column name if you are doing a single pivot. 2014 or 2015 as the column name is self explanatory and easy to deal with programatically. However, I will use the #Years table to show how to rename the column header dynamically.

(YearID INT, Description VARCHAR(128))

INSERT INTO #Years (YearID, Description) VALUES (1, '2014')
INSERT INTO #Years (YearID, Description) VALUES (2, '2015')

Finally #Sales. Note the revenue figures, these are semi-made up but I believe to be in the ballpark range. Yes it is a lot of money! Here is our #Sales table

(ManufacturerID INT, YearID INT,Revenue MONEY)

INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,2,59000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,2,46000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,2,111500000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(1,1,55000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(2,1,42000000000)
INSERT INTO #Sales (ManufacturerID, YearID, Revenue) VALUES(3,1,101500000000)

Next we need to build some strings for the columns to be used in our dynamic SQL statements

--get delimited column names for various SQL statements below
	-- column names for pivot
	@PivotColumnName= ISNULL(@PivotColumnName + N',',N'') + QUOTENAME(CONVERT(NVARCHAR(10),YearID)),
	-- column names for insert into temp table
	@TempTableColumnName = ISNULL(@TempTableColumnName + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)),
	-- column names for alteration of temp table
	@AlterTempTable = ISNULL(@AlterTempTable + N',',N'') + QUOTENAME('Y' + CONVERT(NVARCHAR(10),YearID)) + ' MONEY' 

I probably could use a single string rather than three, and do some string replacements in every usage, but I figured the above is the clearest. In this case I prefix the YearID with a ‘Y’.

Next, create our Pivot temp table

	 ManufacturerID INT

-- Thats it! Because the following step will flesh it out.

SET @SQL = 'ALTER TABLE #Pivot ADD ' + @AlterTempTable
EXEC sp_executesql @SQL

Note: If for some reason you did not know any field names in advance, you could create the temp table with a IDENTITY column that you just ignore, like

     IgnoreMe INT IDENTITY(1,1)

Now we can just execute the dynamic PIVOT

--execute the dynamic PIVOT query into the temp table 
SET @SQL =  N'
	INSERT INTO #Pivot (ManufacturerID, ' + @TempTableColumnName + ')
	SELECT ManufacturerID, ' + @PivotColumnName + '
	FROM #Sales S
	  FOR S.YearID IN (' + @PivotColumnName + ')) AS PivotTable'
EXEC sp_executesql @SQL

Now we can return our results like so

SELECT M.Name, P.*
FROM #Manufacturers M
INNER JOIN #Pivot P ON M.ManufacturerID = P.ManufacturerID

This results in

Finally, placing the a leading character in the column name may seem like an unnecessary step, but if you had two dynamic pivots in your stored procedure, say one has CategoryID as the column header and another had RegionID, or another key field in the database as the header, how to tell them apart if you returned as a single joined result? A clash is a possibility if CategoryID=1 exists and you also have RegionID =1. Using a C and R prefix in that case will prevent clashes when joining the temp tables.

ASP.NET Core 1.0

Since my last post the name has been changed to ASP.NET Core 1.0.

Starting over to use the latest & greatest I uninstalled the previous packages in Ubuntu

I then Installed the Debian package from

I downloaded the latest samples from David Fowler

Currently the HelloWeb code isn’t restoring for me (Unable to restore Microsoft.AspNetCore.Server.Kestrel), I assume this is because of as this moment the Ubuntu code isn’t building according to the CLI page above.

Instead, if I use my previous simple console app from here, and change the project.json to

    "version": "1.0.0-*",
    "compilationOptions": {
        "emitEntryPoint": true
    "dependencies": {
        "NETStandard.Library": "1.0.0-*"
    "frameworks": {
        "dnxcore50": { }

And then change to the project directory and restore using the latest bits

dotnet restore -s

It will build and run.