Wednesday, 21 November 2012


Part of my new SQL 2012 TSQL features series is a look at the new FORMAT function. 
This function can be used to format date/time and number values as strings.
Almost all SQL developers will come across wanting to use SQL to format their dates in a specific way or make numbers more presentable.
It has a very simple syntax and does pretty much exactly what you'd hope. 

See the documents online for more usage examples.


Part of my new SQL 2012 TSQL features series is a look at the new CONCAT function.
CONCAT ( string_value1, string_value2 [, string_valueN ] )
From the documents online:
CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. 
This means it's now possible to easily concatenate strings without worrying about fields being null.  Previously if you used a field that was null when adding strings together it would have resulted in a total null string.  This meant using some custom logic to deal with the null, like the following.
'A' + case when [field] is null then '' else [field] end + 'B'
The new string CONCAT function neatly does away with this as shown below in a very basic example.

From the final result set you can see that including a null in a list of things to concatenate no longer results in a NULL value as it did previously as seen in the second result set.

Tuesday, 16 October 2012

Backup and Free File Sync

Backups...backups...backups... So critical, yet so difficult to be 100% confident. Simple answer is there is no one solution that will guarantee your data's safety.

Storing your data using RAID mirroring will help avoid annoyances of hard drives dying horribly at the wrong moment, but this doesn't give you any protection against fat fingers and removing a whole directory by accident.  In fact my 3 year old daughter deleted a whole folder of music from a share on my NAS by accident just my leaning on the keyboard, yikes!

In my quest to enable simple file folder backups of data held on my NAS to give me an off-site backup, I still find the easiest solution is to copy it all to an external hard drive and hike it over to a friends house.

There are many file copy solutions, a solid one, probably built into your Windows operating system is Robocopy. This allows transfer of files and directories recursively including mirroring, where it will remove any files at the destination which are no longer at the source. If you don't using the mirroring option and just copy all your files, your still just overwriting the existing files, so what we really want is some kind of versioning.

Now I don't need anything complicated and I'm an avid fan of free software so I recommend you give Free File Sync a whirl. It's got a nice GUI, has all the file synchronising features you can think of, specifically including the option to version files you copy over, so if it can see the file already exists at the destination, it'll create a separate timestamped version, you can also specify a limit of how many historical copies it will keep. Certainly a nice way to keep track of things for a while without .

Don't forget, the only solid backup, is a tested backup and one that isn't at the same location as your current data; use a friend, use the cloud (DropBox, SkyDrive, Google Drive all have free storage options for at least your most important files).

Don't wait till you lose everything to wish you'd done it sooner!

Thursday, 11 October 2012

Windows 8 Keyboard Shortcuts - [WIN + X] - Power user commands

Now that everything is relocated within Windows 8 and a little bit harder to find at first, pressing the X key while holding down the Windows key brings up one of the more useful menus.

The "Power user commands" menu

Saturday, 6 October 2012

Windows 8 Start Menu - Replace it with the best of both worlds!

After using Windows 8 for a few months now, I still feel the main start menu replacement is a little full-on and isn't as discrete as it could be. I suggest you try out Classic Shell. Unlike some other start menu replacements for Windows 8, it's free, and you can easily still get to the built in Windows 8 start screen by pressing Shift with the Windows key. Completely configurable, definitely worth a try, especially given the price.

Friday, 5 October 2012

Skype Home shows just a rotating circle

For the last week I noticed that the "Skype Home" tab that comes up in the main Skype interface ( was just showing the rotating loading circle.
Turns out that this is a problem with Internet Explorer settings and resetting them fixes the problem.

  • Go into Internet Properties within Internet Explorer and go to the "Advanced" tab. 
  • At the bottom is a "Reset..." button. Click that and reset your settings back to defaults. 
  • Quit Internet Explorer.
  • If running, quit Skype.
  • Start Skype again and all should be well.

Thursday, 4 October 2012

Alternative to...

I've used this really handy website for many years now to find other pieces of software out there that do a similar job to something I've already got. A great way to find software that could be preferable to a tool you already use.

Friday, 31 August 2012

Cisco VPN client on Windows 8

I posted previously about the wonders of getting Cisco VPN client to work 'out of the box', there was initially trouble with Windows 7 and it's no different with Windows 8...

The symptom is that when you try and connect it complains that it can't enable the adapter:
"Reason 442: Failed to enable Virtual Adapter".

The problem is that the name of the adapter in the registry has got some corruption and so the client software can't find the entry to enable.

Open regedit and find HKLM\SYSTEM\CurrentControlSet\Services\CVirtA

In there you'll see DisplayName which has some funky information on the front of it. It should just be Cisco Systems VPN Adapter so remove any junk from the front and you should be good to go.

No restart of the system required for this one, or even the client for that matter, just try and reconnect and it should work.

Wednesday, 29 August 2012

Windows 8 change the monitor the 'Modern' start screen appears on

I finally found out how to change the monitor that the 'Modern' start screen appears on in Windows 8. There used to be an option in the context menu on the taskbar to set the monitor as where the new start menu appears, but that seems to have gone in the final release.
This was an issue for me as one of my monitors is a touch screen and it makes sense to align that with the new 'Modern' interface.

Press the [windows key] + [page down] to change the monitor used!

Tuesday, 10 April 2012

Remote Desktop Connection Manager

There are many commercial solutions around for managing remote desktop connections, there is also a lesser known free tool from Microsoft called Remote Desktop Connection Manager that handles most of your day to day needs.

You can save various configurations against groupings of servers allowing the terminal services settings to be defined at the root, per group of servers or on a server by server basis. This allows saving of credentials for a specific group of servers which can be very handy and make connecting easier to manage.

All the usual terminal services settings are in there so configuration can be quite granular. Enough chat though, here's the link:

Monday, 20 February 2012

MSSQL: Drop all active connections to a database

In Microsoft SQL Server you can drop all active connections to a database, prior to restoring it for example, by simply taking the database offline and bringing it back online again immediately.

USE master


Tuesday, 24 January 2012

VS2010 project runs slow when attached to debugger

I had an interesting problem occur to me today. I was using a new installation of Visual Studio 2010 Ultimate onto a machine other than my usual development machine. I needed to do some performance analysis on a machine that was nearer to a deployment configuration.
After copying the code project over and running the project as normal inside the debugger, I found it to be extremely slow. General interaction was a bit sluggish, but the chunks of code that did some serious work were crawling. Running the program outside the debugger (CTRL+F5 instead of F5) found the application to run at normal speed.
After a couple of reboots and head scratching, upon investigation I found that people were suggesting to delete any breakpoints, so I went to the debug menu to delete all, but it was greyed out (I assume because no breakpoints were present).
So I added a breakpoint on a line of code and then went to the menu option and removed all breakpoints. Strangely after this the code started working at full speed.

Thursday, 19 January 2012

Digital SLR Cheat Sheet

Here's a handy cheat sheet that helps quickly remind you of what all those fiddly settings on your shiny new SLR do.

Thursday, 12 January 2012

VS2010 Keyboard and Mouse Shortcuts

Keyboard and mouse shortcuts are critical to fast coding in VS2010:

Useful frequently used shortcuts for me are:
Ctrl+K -> Ctrl+C = Comment code
Ctrl+K -> Ctrl+U = Uncomment code
Shift+F12 = Find usages
Ctrl+] = Find matching brace/bracket
F9 - Add/remove breakpoint
F12 - Go to definition
Ctrl+K -> Ctrl+S = Surround code with... choose snippet.