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
        '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

        '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

    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

‘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

Getting SQL 2012 SSIS packages built on TFS 2012.2

I have been trying to get SQL 2012 SSIS packages built on a TFS2012.2 build system. As has been pointed out by many people the problem is you cannot build SQL SSIS packages with MSBuild. This means you have to resort to call Visual Studio DevEnv.exe from within your build.

Jakob Ehn did a great post on this subject, but it is a little dated now due the release of VS 2012 and SQL 2012

The command line

But before we get to TFS, let us sort the actual command line we need to run. So assume VS2012 is in use, the basic command line build for a solution will be

“C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv”  “MySolution.sln” /build "Release|Any CPU"

If you solution only contains SSIS packages then this command line might be OK. However you might just want to build a single SSIS project within a larger solution. In this case you might use

“C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv”  “MySolution.sln” /build "Release|Any CPU"  /project “SSISBits\SSISBis.dtproj”

So to work out the command line you need,  first you need to make sure VS2012 and the Business Intelligence tools are installed on your build box. Once this is done you can try the command line. I decided for my project that I would create a second solution file in the root of the source code that just contained my two SSIS projects, thus making the command line easier (basically one solution for SSIS packages and another for everything else).

So I ran the command line

“C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv”  “MySolution.sln” /build "Release|Any CPU"

and got

Microsoft (R) Microsoft Visual Studio 2012 Version 11.0.60315.1.
Copyright (C) Microsoft Corp. All rights reserved.
Error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
Error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
========== Build: 0 succeeded or up-to-date, 2 failed, 0 skipped ==========

Not good. So I checked that if I loaded the same solution in the same copy of Visual Studio 2012.2 it built OK, and it did.

So it seems there is an issue with command line build of SSIS packages in VS2012. A quick search showed it was a logged issue on Microsoft Connect. Luckily a workaround was mentioned, so I tried it, to use the VS2010 version of the tools. So my command line became

“C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv”  “MySolution.sln” /build "Release|Any CPU"

To try this I had to install the SQL Data Tools from my SQL 2012 ISO (not the SSDT tools from the web as these free ones don’t have the BI features). Once this had installed I could issue my command line and it all built OK.

So I knew I had a working command line. I started to put the same version of VS2010 SSDT tools on my TFS build box and moved onto the build process.

The TFS Build Process

So as now I had the command line, I could apply this knowledge to the process Jakob outlined. There are two basic steps

  1. Run the command line build – this was basically the same
  2. Find the files created and copy them to the drops location – the change here is the old post mentions .MSI files, now we are looking for .ISPAC files

As I had decided to have two solutions within my build, I used an if block (based on a solution name convention) to choose if needed to do a MSBuild or DevEnv build. So my process flow for the build phase was.



Also I had to edit the xcopy block to look for .ISPAC files extensions i.e.


Other than these changes the templates was exactly as Jakob described – even down to using VS2010!


So once all this was done I had a build that create my SSIS packages.

All seems a lot of work, life would be so much easier if SSDT

  • Work properly under VS2012
  • Or even better support MSBuild!

Stupid gotchas on a SQL 2008 Reporting Services are why I cannot see the Report Builder Button

There is a good chance if you are using TFS that you will want to create some custom reports. You can write these in Reporting Services via BI Studio or Excel, but I wanted to use Report Builder, but could not see the Report Builder button on this Reporting Services menu


The problem was multi-levelled

First I had to give the user access to the Report Builder. This is done using folder property security. I chose to give this right to a user (along with browser rights) from the root of the reporting services site


But still no button. Forums and blog posts then talk about changing options on the ‘Site Settings’ menu, the above screenshots shows that this is also missing from the top right.

To get this menu option back, I had to run my browser as administrator and then this option appeared. Turns out that the TFS Setup user I was using  had not been made a Reporting Services site administrator, just a content administrator.

But still this was not enough, I also add to add users as System Users to allow the Reporting Services button to appears. So my final Site Settings > Security options were


Once all this was done I got my Report Build button and I could start to write reports.

Recording of my SQLBits Session on Visual Studio 2008

A webcast recording of SQLBits IV session ‘Making the SQL developer one of the family with Visual Studio Team System’ is now available on the SQLBits site. This discusses the features of the VS2008 Database GDR Edition.

Unfortunately I will not be proposing a session for this years SQLBits community event on the 21st of November 2009 at Celtic Manor in Newport, as I will be travelling back from the Microsoft PDC in LA


Fun with a SQLExpress 2005 upgrade 2008

On my development PC I had a 2005 instance of SQLExpress that was installed as part of the VS2008 setup. I thought I had upgraded it when I put on the SQL 2008 Management tools and/or VS2010 beta, but it seems I didn’t. I thought I would try the new Microsoft Web Platform Installer, but this also thought I had done the upgrade to 2008, I suspect due to the fact I had the 2008 management tools.

Note: If you are using the Microsoft Web Platform Installer 2.0 RC remember you can’t just click on it to run from the web if you are running as a non-administrator user on your PC (as you should be, running least privilege). You need to download it and ‘run it is administrator’ or open it in a browser running as administrator to get it t even load.

So I needed to download the SQLExpress 2008 media to do a manual upgrade, as I remembered I could not use the developer edition media I had to hand to upgrade and Express instance. This download in itself proved problematic. I did a downloaded from MSDN, but the file I got gave a ‘not a valid win32’ error when I tried to run it. Also I noticed each time I tried to download it in IE8 it was a different size – not a good sign! Once I swapped to Firefox it downloaded without issue.

Anyway in the end I got the right media and access rights and the upgraded went smoothly. However then I tried to attach a 2008 DB (the reason I needed the upgrade in the first place) I got the error

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)


I had yet again forgotten to run SQL Management Studio as an administrative user. This error dialog is SQL Management Studio was of saying you don’t have the rights!

Visual Studio 2008 Database Edition GDR release – Createdeployment

Whilst preparing for my session at SQLBits next weekend I was re-watched Gert Drapers’ PDC session (TL45) where he used a command tool to deploy a database via a USB pen drive (about 30 minutes into the session). Now it seems that the createdeployment command line tool he used is not currently available outside Microsoft, but the same effect can be achieved use the VSDBCMD command.

Step 1 – get the files onto the distribution device

The first step is to build the distribution media, this is just an XCOPY process. As the MSDN documentation says you need to end up with the following directory structure on your USB drive; for this example I used G: for USB drive letter and Database1 for the name of the database I want to distribute

G:\ copy the contents of [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Deploy & sub directories
G:\ copy the dlls from[Program Files]\Microsoft SQL Server Compact Edition\v3.5 folder
G:\Database1\ copy the contents of [ProjectsFolder]\DataBase1Solution\Database1\sql\debug or release directory after the DB project is built

Step 2 – A script to do the deploy

It is now a simple process of running the command line tool, but this is a bit long to type each time so I used a batch file. My command usage was

deploy [DB name] [SQL server instance]
e.g. deploy database1 .\sqlexpress

The contents of the actual deploy.bat batch file is as follows. Note there are many more options you can set but this seems to be the basic minimum

VSDBCMD /a:Deploy  /dsp:Sql /cs:"Server=%2;Database=%1;Trusted_Connection=yes;"  /model:%1\%1.dbschema  /manifest:%1\%1.deploymanifest /script:%1%.sql /dd

Using this batch file a new instance of a database can be created or an existing one updated.

Note: When I first tried to get this going I keep getting SQL file create errors which appears as TSD01268 errors in the deployment log. Eventually I realised the problem. I was running on a 64Bit Windows 7 PC. My default SQLExpress instance, running as the Network Service account, was setup to and had rights to create files in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data but not in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Unfortunately the VSDBCMD tried to use the second location. Once the SQL instance was set to default to the second location and suitable rights provided all worked correctly.