CASE WHEN “I need to learn more T-SQL” THEN GoogleTheResult() END

One of my favorite recent discoveries in my SQL Server work recently, is the awesome T-SQL “CASE…WHEN…THEN” statement. I can’t describe how much I love this thing.

It’s pretty simple really:


SELECT
CASE
WHEN fruit = 'apples' THEN 'not oranges'
WHEN fruit = 'oranges' THEN 'not apples'
ELSE 'neither apples nor oranges'
END AS fruit
FROM fruits

As can be expected, for every row where the column is set to “apples”, this will output “not oranges”. For those with the value “oranges”, it will output “not apples”, and of course, any other value (such as “bananas”) would output “neither apples nor oranges”.

To make things more interesting, you can combine the comparison portion (i.e. x = y) with any WHERE based comparisons, such as:

  • X IS NULL
  • X IN (value1,value2)
  • X >= 7
  • X =1 AND >=27
  • X+Y>27 OR ISNULL(Z,0) = 0
Using the comparison, you can even output different columns as the result. I.e.
  • WHEN columnA is null THEN columnB
  • WHEN columnA+columnB > 27 THEN columnB/(SELECT MAX(columnA) FROM TABLE)

Thanks Google. I really love this statement, it’s allowed for some pretty funky complex T-SQL scripts recently, and made my life alot easier!

Backing up all SQL Server user databases to file

The ultimate lazy backup script. I wasn’t feeling to selective about which databases to backup, and I figured, it’d be best to back them all up, even that testing database I occasionally use for data import/export. Never know when I might need it again…

This sits inside a SQL Server Agent job, and runs nightly. Simple enough really.

All works as expected on SQL Server 2012.

DECLARE @Database varchar(max)
DECLARE @path varchar(max)
SET @path = concat(N'D:\BACKUP\',convert(varchar,getdate(),112),' ALL DATABASES BACKUP.bak')
DECLARE my_cursor CURSOR FOR
select name from sys.databases where database_id>4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BACKUP DATABASE @Database TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'SQL Server all user databases backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM my_cursor
INTO @Database
END
CLOSE my_cursor
DEALLOCATE my_cursor

I’m sure there’s a better way to do this, but there doesn’t appear to be a column in the sys.databases table which denotes a database as a system or user database. Instead, I’ve used the WHERE database_id > 4 – which removes master, model, msdb, and tempdb from the backup.

Server Posterpedia Windows 8 Application

You’re designing a new SQL Server infrastructure for a client, but can’t remember if that high availability solution topology requires shared storage or not, and need to quickly look it up without searching through TechNet. How? With this application! Some genius has combined all of Microsoft’s server architecture posters into a handy Windows 8 application which is now available on the Windows App Store here

What’s New in Windows Server 2012 Hyper-V

I recently attended TechEd 2012 on the Gold Coast, and was pretty spoilt for choice as to what technologies I wanted to learn more about. I have an infrastructure background, and recently completed the MCITP Virtualisation Administrator certification for Windows Server 2008 R2, so Hyper-V was still fresh in my mind and one of those technologies I really enjoy using.

I didn’t expect much from the Hyper-V sessions at TechEd. I thought, hey, Hyper-V in Windows Server 2012 really can’t have that much extra, surely… Was I wrong or what!?!?

My first session on Hyper-V, I was flabbergasted by the sheer number of new features and improvements introduced in 2012. As someone who’s always played with VMWare and recently shifted allegiance to the Microsoft side, it comes as no surprise that I continue to be impressed by Microsoft’s hard work in the virtualisation space. Hyper-V continues to innovate and add to it’s enterprise ready feature set, and it truly offers an alternative to other virtualisation platforms. Continue reading “What’s New in Windows Server 2012 Hyper-V”

MCSE Private Cloud Certification

Released a few months ago, Microsoft have re-vamped their certification for the 2012 suite of products, introducing, most notably, the MCSE Private Cloud certification.

For all the MCITP’s, this shouldn’t come as bad news, requiring exorbitant amounts of further study and certification. In fact, it’s quite the opposite.

MCITP SA and EA’s automatically receive the MCSA 2008 qualification, which only requires 70-246 and 70-247 to upgrade to the MCSE Private Cloud certification.

As an MCSA 2008, you can simply study for and complete exam 70-246 – “Monitoring and Operating a Private Cloud with System Center 2012”, and, until January 31st 2013, complete the 2008 equivelant of 70-247, being 70-659 – “Windows Server 2008 R2, Server Virtualisation”.

For more information about the MCSE Private Cloud certification, check out http://www.microsoft.com/learning/en/us/certification/cert-private-cloud.aspx

Building a Hyper-V Lab – cheaply!

I’ve been trying to build a lab at home for a while, but have continued to put the thought aside, over and over. Not because I lack motivation, no, because it’s so damned expensive to purchase so much kit if I’m going to use it for a few months, and then not use it again.

My latest certification path is to complete the MCITP: Server Virtualisation cert, and so the time came for me to start thinking seriously, how am I going to do this? Continue reading “Building a Hyper-V Lab – cheaply!”

Windows 8 – to be or not to be?

Microsoft has a track record for releasing horrible operating systems every now and then, as witnessed by the IT world more recently with Windows Vista. It begs the question, after a very successful Windows 7, what is in store for Windows 8? Will it be king, or will it flop?

My motivation for this article, comes from Mick Huxley’s blog post this morning, “Why Windows 8 will be a flop”. My views here are based on very little experience using the Windows 8 development release installed on a non-touch device and briefly playing with a co-worker’s Windows 8 Slate device, and I will relate my experiences  and views to Apple’s iOS/OSX family and successes. Continue reading “Windows 8 – to be or not to be?”

Lion-style Inverse Scrolling – On Windows XP

My home computer is a Mac, now running OSX Lion. I’ve become quite accustomed to the backwards/reversed scrolling which seems to be the default in Lion – and matches the “natural” swipe up/down gestures you would use on an iPhone or other touch screen device.

Today I was looking around on my work Windows XP machine, and was disappointed to find there is no “invert mouse” option.

So I found this – http://www.howtogeek.com/57542/how-to-get-the-worst-os-x-lion-feature-in-windows-reverse-scrolling/

Setting Up Reverse Scrolling in Windows

To make this work, you’re going to need to make sure that you’ve got AutoHotkey installed, or else the script won’t work. Don’t worry, it’s really lightweight.

Once you’ve got that installed, you can either paste this into a new AutoHotkey script (a *.ahk file), or put it into your existing script. Or you could just download the script we’ve got linked below.

WheelUp::
Send {WheelDown}
Return

WheelDown::
Send {WheelUp}
Return

Save it, double-click the file to run it, and you’ve now got reverse scrolling. Or, you know, just download it.

IIS, PHP, and LDAPS with Active Directory.

Why you’d ever want to do this is probably a discussion best left to the political arena. But for the purpose of that thing I call my “day job”, I was required to do just this.

External website, authenticates against Active Directory using LDAPS. Website is coded in PHP, and runs on IIS on Windows Server 2008 R2 x64.

In the rest of the world, this is an Apache deal, but limited by internal support, it has to be IIS and Windows. Continue reading “IIS, PHP, and LDAPS with Active Directory.”