T-SQL UNPIVOT’ing columns with different datatypes

Well, you can’t, really. What you have to do is to make sure that all the the columns have the same datatype and then perform the unpivot.

Here is the table I want to partially unpivot:


So what I do is to cast all columns to a suitable common format, in this case VARCHAR(100), before the actual pivoting:

SELECT Id, ColumnName, Value
        CAST(SysNr AS Varchar(100)) SysNr,
        CAST(KredsNr AS Varchar(100)) KredsNr,    
        CAST(EjendomsNr AS Varchar(100)) EjendomsNr,    
        CAST(Navn AS Varchar(100)) Navn,
        CAST(EjendomStatusId AS Varchar(100)) EjendomStatusId,
        CAST(Aktiv AS Varchar(100)) Aktiv
    FROM Ejendom
) AS S
    Value FOR ColumnName IN (SysNr, KredsNr, EjendomsNr, Navn, EjendomStatusId, Aktiv)
) AS U

Voila – quite a bit of typing, but pretty easy peasy otherwise.


Switching your IP setting to static with PowerShell


In our Hyper-V test environment we use a mixture of dynamic and static IP addresses. Some servers need to have static IP addresses and in order to facilitate the process of provisioning a new server I wanted to have a script that toggles the current network adapter setting from dynamic (DHCP) to static.

Do remember to mark that IP in the DHCP server as unavailable as well in order not to get IP confilcts…

Write-Host "Switch Current IP configuration to to Static IP"

$wmiip = Get-WmiObject win32_networkadapterconfiguration -filter "DHCPEnabled = 'true' and ipenabled = 'true'"

if ($wmiip)
    Write-Host "Switching..."

    $ipaddress = $wmiip.IPAddress[0] 
    $ipgateway = $wmiip.DefaultIPGateway[0]
    $ipdnsordr = $wmiip.DNSServerSearchOrder

    $wmiip.EnableStatic($ipaddress, "")
    $wmiip.SetGateWays($ipgateway, 1)
    Write-Host "Static already set"

Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled = 'true'"
"DNS              : " + $wmiip.DNSServerSearchOrder

Write-Host "Done..." 


This script was inspired by an article by Scripting Guy

T-Sql to generate a table of datetimes (time series)

The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.

What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.

        ROW_NUMBER() OVER (ORDER BY Id) AS Number,
            ROW_NUMBER() OVER (ORDER BY Id), 
            DATEDIFF(DAY, 0, GETDATE()))) AS DateTime
        FROM [DB]..[LargeTable]

The number 86399 corresponds to  the number of seconds (One day = (60*60*24) – 1) to generate in the table.

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 18,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 7 sold-out performances for that many people to see it.

Click here to see the complete report.

Removing orphaned users and logins from SQL Server

I’ve just spent some really fun hours trying to clean up users and logins from both our production and test-servers. Over the course of several years and restores from production to test, a number of orphaned users and logins have accumulated, that is, database users that do not have an associated login and logins that do not have any associated database users.

The following script attempts to identify both by extracting users (and user roles) from all databases and comparing them with the server logins. The output suggests the Sql statements to clean up the mess, but do peruse it throughly before executing!

Nuff said, here is the code:

Code Snippet
  1. — Script to assist cleaning up orphaned Logins and Users on Sql Server
  3. — Query to extract Users and Roles
  4. DECLARE @sqlquery varchar(500)
  5. SET @sqlquery =
  6.     'SELECT DB_NAME() AS DatabaseName, P1.name AS UserName, P2.name AS UserRole FROM sys.database_role_members DRM '
  7.         +'JOIN sys.database_principals p1 ON P1.principal_id = DRM.member_principal_id '
  8.         +'JOIN sys.database_principals p2 ON P2.principal_id = DRM.role_principal_id '
  9.         +'WHERE P1.type IN (''S'', ''U'') AND LEN(P1.sid) > 1'
  11. — Query to extract User-databases only, ie not master, tempdb etc
  12. DECLARE @dbname char(50)
  14.     SELECT Name FROM sys.databases WHERE LEN(owner_sid) > 1
  16. — Temporary table to hold all Database Users and Roles
  20. (
  21.     DatabaseName varchar(100),
  22.     UserName varchar(100),
  23.     UserRole varchar(100)
  24. )
  26. — Loop over databases with a cursor
  27. OPEN c1
  28. FETCH NEXT FROM c1 INTO @dbname
  31. BEGIN
  32.     PRINT 'Adding ' + @dbname
  33.     EXEC('USE [' + @dbname + ']; ' + 'INSERT INTO #USERNAMES ' + @sqlquery);
  35.     FETCH NEXT FROM c1 INTO @dbname;
  36. END
  40. — Logins not used in any databases. NOTE a specific login is excepted
  42. SELECT Name, 'DROP LOGIN [' + Name + '];' AS Sql
  43.     FROM sys.server_principals P
  44.     LEFT OUTER JOIN #USERNAMES U ON U.UserName = P.Name
  45.     WHERE type = 'S'AND is_disabled = 0 AND LEN(sid) > 1 AND U.DatabaseName IS NULL AND name NOT IN ('udvikler')
  47. — Users in databases without server Logins. NOTE a specific login is excepted
  49. SELECT DISTINCT DataBaseName, UserName, 'USE [' + DatabaseName + ']; DROP USER [' + UserName + '];' AS Sql
  51.     LEFT OUTER JOIN sys.server_principals P ON U.UserName = P.Name
  52.     WHERE name IS NULL AND UserName NOT IN ('dbo')

Exclusive access could not be obtained because the database is in use.

The ability to restore a SQL Server database over and over can sometimes be handy in a test scenario, for instance when you need to test  a schema-update for autodeployment through DbUp/Octopus – or you otherwise need to have your database in a known state prior to testing.

However this is quite often blocked because the database is in use, resulting in the above message.

My solution to this is to script the restore to run in single user mode like so:

Code Snippet
  1. USE [master]
  3.   RESTORE DATABASE [BenchmarkDB] FROM  DISK = N'D:\BenchmarkDB.bak' WITH  FILE = 1,  MOVE N'BenchmarkDB_log' TO N'D:\Data\BenchmarkDB.ldf',  NOUNLOAD,  REPLACE,STATS = 5
  5. GO


Update 2014-09-01:

To achieve the same in Management Studio then go to Options and check this box:


To check whether your database got correctly reset to Multiuser Mode, go to Database Properties > Options, and look at the “State” subgroup, “Restrict Access” item (you’ll need to scroll down to the bottom of the list to see it):


Setting Execution Timeout in DbUp

DbUp is a nice open source project that allows you to deploy Sql-scripts via a .Net  executable, thus enabling deployment via a pipeline like Octopus Deploy. DbUp tracks which scripts have already been run on your database so you don’t have to worry about double-updates to your database.

The basic problems with idempotency and databases remain of course, but that is a completely different story and you still have to manage that.

DbUp has a nice fluent interface, but doing programmatic configuration unfortunately rather breaks that. It took me a while to figure out so here is how I managed to change the excution timeout in my DbUp deployment C# program for a long running script.

First use DeploymentChanges.To.SqlDatabase(connectionstring).WithScriptsEmbeddedInAssembly( … ) to create a variable which is an UpgradeEngineBuilder.

Then invoke the Configuration callback action on the UpgradeEngineBuilder like so:

upgradeEngineBuilder.Configure(c =>
    c.ScriptExecutor.ExecutionTimeoutSeconds = 30 * 60; // 30 minutes in seconds
    Console.WriteLine("Configure ExecutionTimeoutSeconds to " + c.ScriptExecutor.ExecutionTimeoutSeconds);

After that you can use the fluent interface again to invoke the Sql-script:

var upgradeEngine = upgradeEngineBuilder.LogToConsole().Build();

var result = upgradeEngine.PerformUpgrade();

Windows 8 RDP – Your credentials did not work

This is a very annoying thing to encounter when connecting to a Remote Desktop on a machine in the network. That green bar just goes round and round for ages! I don’t know why this happens – in my case it is a machine in another domain that I log in to:


Let us say that this machine resides on IP address, then the following Powershell snippet will fix your woes:

Code Snippet
  1. Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Terminal Server Client\Servers\' -Name UsernameHint -Value ''
  2. mstsc.exe /v:

There are other ways to go about this, such as the REG command in a batch-file, but these days doing .bat files is just soooo old school 🙂

Referencing another database in SSDT – error SQL71561

If you have SQL Server database project in Visual Studio that needs to reference another database, ie a View containing a 3 level reference like [OEDB].[dbo].[Inventory], you need to reference the other database by right-clicking “References” and choosing “Add Database Reference”.

This brings up this screen:


Near the bottom is something called “Database variable”. If this is filled (which is the default) all references to that database in the imported SQL must go via the variable, as can be seen from the example just below [$(oedb)].[Schema1].[Table1] the “Database variable” field.

If you fail to correct occurrences in your SQL from [DatabaseName] to [$(DatabaseName)] you will probably experience an error list with hundreds of SQL71561 errors like this:

SQL71561: View: [dbo].[qryCreateYMD] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [OEDB].[dbo].[Inventory].[Id], [OEDB].[dbo].[Inventory].[OEDB]::[dbo].[Inventory].[Id] or [OEDB].[dbo].[Inventory].[OEDB]::[dbo].[Inventory].[Id]

In my mind this wasn’t really the expected behavior, but what you can do to just use the references you have without changing your views, is to simply clear out the “Database variable” field:


As can be seen from the Example usage, references wil now work as expected…

This is basically what is explained here (when I finally found the explanation), but I wanted to add the screenshots of how this works.

Enable .Net 4.5 in IIS on Windows 8.1

Setting up a new development box for myself I had forgotten all about the necessity to use the aspnet_regiis.exe –i  command. The information is fairly easily available, but with this post I’d like to cut away the fat and stick to a very easy how-to.

On my Windows 8.1 aspnet_regiis resulted in this message:

Microsoft (R) ASP.NET RegIIS version 4.0.30319.33440
Administration utility to install and uninstall ASP.NET on the local machine.
Copyright (C) Microsoft Corporation.  All rights reserved.
Start installing ASP.NET (4.0.30319.33440).
This option is not supported on this version of the operating system.  Administrators should instead install/uninstall ASP.NET 4.5 with IIS8 using the "Turn Windows Features On/Off" dialog,  the Server Manager management tool, or the dism.exe command line tool.  For more details please see
Finished installing ASP.NET (4.0.30319.33440).

Googling around a bit more turned up this link that explains how to go to the Windows Features (via appwiz.cpl) to activate .Net 4.5 on IIS:


Or to run this from an administrative prompt:

dism /online /enable-feature /all /featurename:IIS-ASPNET45

Please note the /all parameter which is not mentioned in the KB article, but necessary to automatically include all the necessary components.


In order to get my webservices running I have spent more hours on struggling with the IIS beast. It appears that (according to this) you have to have to switch on yet another feature:


Or, if you want to automate all this, more dism commands:

dism /online /enable-Feature /all /FeatureName:WCF-HTTP-Activation

dism /online /enable-Feature /all /FeatureName:WCF-HTTP-Activation45