SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

January 2013 - Posts

Photo cataloguer

If you are anything like me, by know you will have a few thousand digital images spread across multiple directories and heaven knows how many of those are duplicates!

Here’s a little powershell script I use to try and keep them in order,  it recursively scans a directory (hardcoded to D:\photodrop) and interrogates any .jpg file for its taken date and time and moves the file to an appropriately named folder /CCYY/MM/DD.

Naturally, I cannot be held responsible if your precious memories get lost by using this script ;)

Let me know how you get on…

[reflection.assembly]::loadfile( "C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll")


function CreatePath ($folderPath){

    $Path1 = $folderpath.Split("\")
    $BuildPath = ""
    for($x=0;$x -lt $Path1.count;$x+=1){
        $BuildPath = $BuildPath += $Path1[$x] +"\"
        if($objFSO.FolderExists($BuildPath) -eq $FALSE){
            $objFSO.CreateFolder($BuildPath) | Out-Null


function MakeString {
    $s="" ;
    $x = $args[0]
    for ($i=0 ; $i -le $args[0].value.length; $i ++)
        $s = $s+ [char]$args[0].value[$i]
    return $s
$objFSO = New-Object -ComObject Scripting.FileSystemObject
foreach($File in Get-ChildItem -Path D:\PhotoDrop -Filter *.jpg -Recurse ){
    $ImgBlob = New-Object -TypeName system.drawing.bitmap -ArgumentList $File.FullName
    $Date = MakeString($ImgBlob.GetPropertyItem(36867))
    try {
        $Dir = $Date.Substring(0,10)
        $Dir = $Dir.Replace(":","\")
        $folderPath = "D:\Photos\"+$Dir
        #check to see if it is missing
        if($LastFilePath -ne $folderPath){
            CreatePath $folderPath
            $LastFilePath = $folderPath
        $NewFileName = $folderPath+"\"+$File.Name
        if($objFSO.FileExists($NewFileName) -eq $FALSE){


Scaling out SQLLunch UK

As you may be aware, I have been running a lunch time user group ‘SQLLunch UK’  having stolen drawn inspiration from Patrick Leblanc (t|b) ’s SQLLunch webcasts.  These however are not traditional webcasts, as although we use remote speakers,  to watch you have to get away from your desk for lunch and come down to the venue.  I know that many people would like to watch from their desk but there are many webcasts and I think that this formula does offer something different.

We have been London based since inception and now the opportunity has arisen to scale it out to another location.   Steph Locke, who run the Cardiff user group, has accepted the challenge and will now be holding SQLLunch’s in Cardiff at the Buffalo Bar 11 Windsor Place, Cardiff CF10 3BY.  Having now had a fair bit of experience, im hopeful that there wont be to many extra technical challenges presented by two venues.

Our first SQLLunch with this format is on the 22nd of Jan with Conor Cunningham (b) of Microsoft.

Signup for both the London and Cardiff events is at www.sqlserverfaq.com

Hope to see you there

SSDT – TSQL Unit testing in multiple environments

Late in December, one the long awaited portions of functionality inside SSDT was released to an eager public, that being unit testing.  There has been a lot of noise generated about sql unit testing as a concept recently and hopefully you have decided that that is now the way to go.  Bill Gibson of Microsoft has blogged at quick beginners guide to SSDT unit testing here.

This is not a post about unit testing per se or the operation of SSDT unit testing, but what you need to do to run the same unit tests across multiple environments.  Even the smallest shop will have multiple SQL servers that you will wish to run tests against (DEV, UAT, integration etc) and if you use SSDT for more than 5 minutes you will discover that there is no easy way to aim the tests at these servers.

Jamie Thomson(b|t) has previously blogged about this inside datadude (SSDTs’ predecessor) and what a pain it can be.  Well, it is still a pain and the supported route is still via this method and it does work in SSDT.  I will say two things though,  firstly the config override file has HAS to be called machinename.sqlunittest.config or username.sqlunittest.config nothing else will do, and secondly the file is very sensitive to formatting.  I had a single leading whitespace in mine, which meant that the file couldn’t be processed correctly ! Also note that the element naming has changed to SQLUnitTesting_VS2010 ( im not running VS2012 to see if it is still the same there)

As I say, this does work, but is not very clear and quite limiting in scope.  I can only have effectively one override file and what if i have a centralised CI build server I wish to run our tests from against multiple environments ?  Well, without more confusing workarounds (that I can think of but haven't tried) you cant.

So, do we have any other options ?

Our friends in the application development world have faced and solved this problem,  though I don't believe their problems are exactly analogous, but this is probably the simplest route to solve it.  Using a VS plugin called Slow Cheetah the app.config file can be transformed at build time to support multiple environments.  Each build configuration (Debug / Release) can have a transform for app.config, after installing the plugin and R-Clicking on the app.config, you should see an ‘Add Transform’ option.


After selecting that, for each of your build configurations (you can always add more) you will see an app.buildname.config file.


The transform to be added to files is:

<?xml version="1.0" encoding="utf-8" ?>

<!-- For more information on using transformations

     see the web.comfig examples at http://go.microsoft.com/fwlink/?LinkId=214134. -->

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">


    <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />

    <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />



When you now build and run your tests Slow Cheetah will apply the transforms to app.config and you will connect to the specified server.  Slow Cheetah includes a ‘Preview Transform’ option to aid with development so you don't have to guess Smile  To me this seems like a much more preferable route that the mstest overriding method.

This is all well and good, but what if you want even more control. 

Disclaimer: I have not tried this any more that detailing it here, I cannot vouch for long term (or even short term) stability.  You are on your own if it all goes wrong.

There is a method by which you can specify exactly which instance/ database to use.  You can programmatically override the connection.  If, in Visual Studio, you select ‘View Code’ on one of your C# unit test modules,  you will see a member called TestInitialize.  Inside this member we can close the existing connection and open one to the server we really wish to test against like so:

        public void TestInitialize()




            base.ExecutionContext.Connection = new System.Data.SqlClient.SqlConnection("Data Source=Server;Initial Catalog=Database;Integrated Security=True;Pooling=False");



Obviously I have hardcoded the connection string here,  but you could have it read from an environment variable or topology file or somewhere else.

Have fun with any of these methods and any one should get you closer to the goal of more reliable code.

Do you know of a more user friendly and workable solution ? I’d love to hear it.

When is a whitespace not a whitespace ?

As I'm sure I must have mentioned in the past, I’m presently involved in a large ALM project at a client.  Part of this project is using Sql Server Data Tools (SSDT) to aid the developer experience and to establish the schema under source control as “The Truth”.

SSDT, has the ability to compare a schema under development to a schema on a server to produce diff scripts and therefore enabling automated deployments,  this is done using SQLPackage.exe .  A big part of this is naturally establishing trust in the software ie SSDT.  We have to be 110% confident that the scripts produced are the right scripts.  Recently though, we had a spurious change appear that took some explaining.

A stored procedure was appearing in a diff script that apparently had no changes, when comparing the project to the schema inside SSDT rather that SQLPackage.exe, I could indeed see that a change had been detected though what that change was, wasn't immediately apparent.  Here is a similar “non-change” :


The developer has tidied up the formatting of the statement ever-so slightly but we have the “IgnoreWhiteSpace” setting enabled.  This change should have been ignored and indeed testing proved that when you change tabs to spaces or add spaces etc ( ie whitespace changes) then the change would be ignored,  so something else was happening.

Loading up a hexeditor and comparing the two I spied another difference that had been overlooked,  the comment had a TAB changed to spaces.  But as I had ignore white spacing enabled therefore shouldn't have been seen.  Testing that this was the culprit was simple enough by turning on the “IgnoreComments”  option, and sure enough, no change.

This ,to me, appears to be a bug,  whitespacing is not ignored in comments when the “IgnoreWhiteSpace” option is set.  For us using “IgnoreComments” is not an option, so we had to live with this change being made to unify the production schema with source control (The Truth).

I did raise a connect item here : https://connect.microsoft.com/SQLServer/feedback/details/774098/ssdt-whitespace-in-a-comment-is-not-ignored-when-using-the-ignorewhitespace-option

but this was closed as “By Design”,  not sure I agree with that assessment but what can you do, eh ?

Hope that this at least saves someone some head scratching on the same issue.