But it works on my PC!

The random thoughts of Richard Fennell on technology and software development

Ordering rows that use the format 1.2.3 in a SQL query

Whilst working on a SSRS based report I had hit a sort order problem. Entries in a main report tablix needed to be sorted by their OrderNumber but this was in the form 1.2.3; so a neither a numeric or alpha sort gave the correct order i.e. a numeric sort fails as 1.2.3 is not a number and an alpha sort worked but give the incorrect order 1.3, 1.3.1, 1.3.10, 1.3.11, 1.3.12, 1.3.2.

When I checked the underlying SQL it turned out the OrderNumber was being generated, it was not a table column. The raw data was in a single table that contained all the leaf nodes in the hierarchy, the returned data was built by a SPROC using a recursive call.

The solution was to also calculate a SortOrder as well as the OrderNumber. I did this using a Power function on each block of the OrderNumber and added the results together. In the code shown below we can have any number of entries in the first block and up to 999 entries in the second or third block. You could have more by altering the Power function parameters

declare @EntryID as nvarchar(50) = ‘ABC1234';

WITH SummaryList AS
    (
        SELECT
        NI.ItemID,
        NI.CreationDate,
        'P' as ParentOrChild,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as OrderNumber,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) *  Power(10,6) as int) as SortOrder
        FROM dbo.NotebookItem AS NI
        WHERE NI.ParentID IS NULL AND NI.EntryID = @EntryID

        UNION ALL

        SELECT
        NI.ItemID,
        NI.CreationDate,
        'L' as ParentOrChild,
        cast(SL.OrderNumber + '.' + cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as nvarchar(100)) as OrderNumber,
        SL.SortOrder + (cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as int) * power(10 ,6 - (3* LEN(REPLACE(sl.OrderNumber, '.', ''))))) as SortOrder
        FROM dbo.NotebookItem AS NI
        INNER JOIN SummaryList as SL
            ON NI.ParentID = SL.ItemID
    )

    SELECT
        SL.ItemID,
        SL.ParentOrChild,
        SL.CreationDate,
        SL.OrderNumber,
        SL.SortOrder
    FROM SummaryList AS SL
    ORDER BY SL.SortOrder

This query returns the following with all the data correctly ordered

ItemID ParentOrChild CreationDate OrderNumber SortOrder
22F72F9E-E34C-45AB-A4D9-C7D9B742CD2C P 29 October 2014 1 1000000
E0B74D61-4B69-46B0-B0A9-F08BE2886675 L 29 October 2014 1.1 1001000
CB90233C-4940-4312-81D1-A26CB540DF2A L 29 October 2014 1.2 1002000
35CCC2A1-E00F-43C6-9CB3-732342EE18DA L 29 October 2014 1.3 1003000
7A920ABE-A2E2-4CF1-B36E-DE177A7B8681 L 29 October 2014 1.3.1 1003001
C5E863A1-5A92-4F64-81C6-6946146F2ABA L 29 October 2014 1.3.2 1003002
23D89CFF-C9A3-405E-A7EE-7CAACCA58CC2 L 29 October 2014 1.3.3 1003003
CE4F9F6B-3A58-4F78-9C1F-4780883F6995 L 29 October 2014 1.3.4 1003004
8B2A137F-C311-419A-8812-76E87D8CFA40 L 29 October 2014 1.3.5 1003005
F8487463-302E-4225-8A06-7C8CDCC23B45 L 29 October 2014 1.3.6 1003006
D365A402-D3CC-4242-B1B9-356FB41AABC1 L 29 October 2014 1.3.7 1003007
DFD4D688-080C-4FF0-B1D0-EBE63B6F99FD L 29 October 2014 1.3.8 1003008
272A46C6-E326-47E8-AEE4-952AF746A866 L 29 October 2014 1.3.9 1003009
F073AFFA-F9A1-46ED-AC4B-E92A7160EB21 L 29 October 2014 1.3.10 1003010
140744E7-4950-43F8-BA0C-0E541550F14B L 29 October 2014 1.3.11 1003011
93AA3C05-E95A-4201-AE03-190DDBF31B47 L 29 October 2014 1.3.12 1003012
5CED791D-4695-440F-ABC4-9127F1EE2A55 L 29 October 2014 1.4 1004000
FBC38F00-E2E8-4724-A716-AE419907A681 L 29 October 2014 1.5 1005000
2862FED9-8916-4139-9577-C858F75C768A P 29 October 2014 2 2000000
8265A2BE-D2DD-4825-AE0A-7930671D4641 P 29 October 2014 3 3000000

VSO now available in a European Azure Data Center

I don’t normal do posts that are just re-posts of TFS announcements, it is much better to get the information first hand from the original post, but this one is significant for us in Europe…

Up to now there has been a barrier to adoption of VSO that the underlying data will be hosted in the USA. Now there are all the usual Azure Microsoft guarantees about data security, but this has not been enough for some clients for legal, regulatory or their own reasons. This has made VSO a non-starter for many European’s where it at first appears a great match.

As of today you can now choose to host your new VSO account in Europe (Amsterdam data center). It won’t remove everyone's worries of cloud hosting, but certainly is a major step in the right direction from a European point of view, addressing many regulatory barriers.

Unfortunately we will have to wait a few sprints to be able to migrate any existing VSO instances, but you can’t have everything in one go!

For the full details have a look at Brian Harry’s and Jamie Cool’s posts

Having fun with my HDMIPI

The HDMIPI screen I supported on KickStarter arrived this week. After a bit of a false start with a driver board that failed after a couple of minutes it is now all up and running. The replacement board arrived in 48Hours – great support service

WP_20141026_002

Now to get down to some projects – that toy robot does look like it needs automating via an IR transmitter and my PiFace

Visual Studio crashes when trying to add an item to a TFS build workflow

There has for a long time been an issue that when you try to add a new activity to the toolbox when editing a TFS build workflow Visual Studio can crash. I have seen it many times and never got to the bottom of it. It seems to be machine specific, as one machine can work while another supposedly identical will fail, but I could never track down the issue.

Today I was on a machine that was failing, but …

But I found a workaround in a really old forum post. The workaround is to load the IDE from the command line with the /safemode flag

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe /safemode

Once you do this you can edit the contents of our toolbox with crashes, and also your template if you wish. The best part is that once you exit the IDE and reload it as normal your new toolbox contents are still there.

Not perfect, but a good workaround

Getting Release Management to fail a release when using a custom PowerShell component

If you have a custom PowerShell script you wish to run you can create a tool in release Management (Inventory > Tools) for the script which deploys the .PS1, PSM files etc. and defines the command line to run it.

The problem we hit was that our script failed, but did not fail the build step as the PowerShell.EXE running the script exited without error. The script had thrown an exception which was in the output log file, but it was marked as a completed step.

The solution was to use a try/catch in the .PS1  script that as well as writing a message to Write-Error also set the exit code to something other than 0 (Zero). So you end up with something like the following in your .PS1 file

param
(
[string]$Param1 ,
[string]$Param2 )

try
{
    # some logic here

} catch
{
    Write-Error $_.Exception.Message
    exit 1 # to get an error flagged so it can be seen by RM
}

Once this change was made an exception in the PowerShell caused the release step to fail as required. The output from the script appeared as the Command Output.

ALM Ranger’s release DevOps Guidance for PowerShell DSC – perfectly timed for DDDNorth

In a beautiful synchronicity the ALM Rangers DevOps guidance for PowerShell DSC has been release at at the same time as I am doing my DDDNorth session ‘What is Desired State Configuration and how does it help me?’

This Rangers project has been really interesting to work on, and provide much of the core of my session for DDDNorth.

Well worth a look if you want to create your own DSC resources.

"The handshake failed due to an unexpected packet format" with Release Management

Whilst configuring a Release Management 2013.3 system I came across a confusing error. All seemed OK, the server, client and deployment agents were all installed and seemed to be working, but when I tried to select a build to deploy from both the Team Projects and Build drop downs were empty.

image

A check of the Windows event log on the server showed the errors

The underlying connection was closed: An unexpected error occurred on a send
The handshake failed due to an unexpected packet format

Turns out the issue was an incorrectly set value when the Release Management server was configured. HTTPS had been incorrectly selected, in fact there was no SSL certificate on the box so HTTPS could not work

image

As this had been done in error we did not use HTTPS at any other point in the installation. We always used the URL http://typhoontfs:1000 . The strange part of the problem was that the only time this mistake caused a problem was for the Team Project drop down, everything else seemed fine, clients and deployment agents all could see the server.

Once the Release Management server was reconfigured with the correct HTTP setting all was OK

image

Cannot build a SSRS project in TFS build due to expired license

If you want to get your TFS build process to product SSRS RDL files you need to call the vsDevEnv custom activity to run Visual Studio (just like for SSIS packages). On our new TFS2013.3 based build agents this step started to fail, turns out the issue was not incorrect versions of DLLs or a some badly applied update, but that the license for Visual Studio on the build agent had expire.

I found it by looking at diagnostic logs in the TFS build web UI.

image

To be able to build BI project with Visual Studio you do need a licensed copy of Visual Studio on the build agent. You can use a trial license, but it will expire. Also remember if you license VS by logging in with your MSDN Live ID that too needs to be refreshed from time to time (that is what go me), so better to use a product key.

‘Unable to reconnect to database: Timeout expired’ error when using SQLPackage.exe to deploy to Azure SQL

I have trying to update a Azure hosted SQL DB using Release Management and the SSDT SQLPackage tools. All worked fine on my test Azure instance, but then I wanted to deploy to production I got the following error

*** An error occurred during deployment plan generation. Deployment cannot continue.
Failed to import target model [dbname]. Detailed message Unable to reconnect to database: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Unable to reconnect to database: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out

Turns out SQLPackage.exe was connecting OK, as if I entered an invalid password it gave a different error, so it had made the connection and then died.

Seems I am not alone in seeing this problem, and most people seem to suggest changing a timeout in the registry or your exported DACPAC. However, neither of these techniques worked for me.

I compare my test and production Azure DB instances, and found the issue. My test SQL DB was created using a SQL Export from the production Azure subscription imported into my MSDN one. I had done a quick ‘next > next > next’ import and the DB has been setup as an Standard (S2) service tier. The production DB had been running as the old retired Web service tier, but had recently been swapped to a Basic tier (it is a very small DB). When I re-imported my backup, but this time set it to be a Basic tier I got exactly the the same error message.

So on my test DB I changed it’s service tier from Basic to Standard (S0) and my deployment worked. The same solution work for my production DB.

Now the S0 instance is just over 2x the cost of a Basic , so if I was really penny pinching I could consider moving it back to Basic now the deployment is done. I did try this, and the deployment error returned; so I think it feels a false economy as want a stable release pipeline until Microsoft sort out why I cannot use SSDT to deploy to a basic instance