MSSQL aggregate on uniqueidentifier type

Update: this refers to MSSQL 2000 - MSSQL 2005 can aggregate on uniqueidentifier, and this article was a result of some scripts that were not backwardly compatible.

MSSQL cannot perform a count(distinct ) on a column of type uniqueidentifier, and returns the following error message:

"The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument"

This was raised on the MojoPortal forums (link) for the following snippet of SQL:

SELECT COUNT(DISTINCT ItemGuid) FROM [dbo].[mp_CommerceReport] WHERE ModuleGuid = @ModuleGuid

The solution is to perform a group, and then a count, as this is effectively what SQL would be doing anyway:

select count(*) from (
select itemguid from [dbo].[mp_CommerceReport]
where ModuleGuid=@ModuleGuid
group by itemguid
) itemlist

Trip to Scotland

Have just returned from a two week trip to Scotland, and all I can say is...wow!

I travelled up via the overnight sleeper service to Aviemore, a small town just off the side of the Cairngorms. I took a basha and a bivi and a bloody good sleeping bag, and spent the first few days camping out and hiking around the lochs and surrounding area. Unfortunately I couldn't do as much wild camping as I'd hoped - I got nabbed by the local warden early in the week as they're reintroducing species of elk into the area, but the weather was fantastic and my gob was duly smacked by the scenery.

I travelled North to Inverness for a few days and explored the pubs and surrounding countryside (not to mention getting horribly hungover on Tennents at a gig on Saturday) before joining Fran and Geoff on the following Sunday for a trip by van around the far North - John O' Groats (the northern-most town) and Dunnet Head (the most northerly point), Durness (the most westerly town) and Orkney, including the famous 5000 year old ruins of the settlement of Skara Brae. A weather front had come in by that point, so there was plenty of traditional Scottish/Orkney rain and wind, with odd patches rolling in the evening. We got lucky though as the weather held out for the majority of the trip.

Balnakiel - out towareds Cape Wrath - is fantastic, white sands, huge waves, and although the tours were shut for the winter season I got a good view of the interestingly named Caves of Smoo and had a good clamber around the surrounding cliffs. The locals were very friendly and chatty, and needless to say I had my good share of haggis and black pudding, and some damn fine Venison steaks courtesy of Fran's excelllant cooking!

The final day was back at Aviemore and a walk half way up Cairn Gorm itself, as well as a trip to the viewpoint on the Funicular. By this point the weather had really started to turn, and there was a good covering of snow on the previously barren mountains, and a good few fog/blizzard storms rolled in: within a space of a minute or two a perfectly clear day with great visibility can turn into practically zero visibility and blizzard conditions. It's no wonder so many expeditions have found themselves in trouble.

Finally, a meal at an Italian restaurant (with, unexpectedly, half a litre of wine...yikes...) was a precursor to the 12 hour return train journey home....

I'm determined to get back to Scotland sometime - what a country! I hope to do a mountaineering course and maybe bag a few Munros to boot....

Watch out for pics on Flickr as soon as I upload them!

More blogger template modification

So, after my previous post on hosting your domain name with Blogger I've been making more customistaions to the site layout, look and feel. I found that the Blogger template system is really good for basic use, and for people with little or no knowledge of HTML and CSS, but when it comes to making more advanced changes - such as adding a favicon (wikipedia) or more interesting CSS stuff - it can get a bit tricky. If you're like me, where you really like to get to the nitty-gritty of it all, it can be a right pain in the backside!

It's not immediately obvious how you can go about doing all this for free without shelling out more of your hard earned moolah but, with the help of Google pages, you can. Google very nicely provides you with 100MB of upload space that you can use to create pages using (yet another) simplified no-brainer interface, but the difference here is that you can also upload files. This means that you can upload (yup, you guessed it) CSS, HTML, JPG and even a good old favico, and then modify the XML of your Blogger template to reference this directly. Well, OK, so it's not really XML, more like XHTML with template bits in, but you get the idea.

The first thing I did was strip out all the messy CSS gumpf at the start of the template. This should really be in one (or more) CSS files.

Next, all the variable tags sections and the rest of the now-redundant CDATA section can go. Google requires at least one b:skin tag, but it doesn't need to have any data, so I just removed the entire b:skin section that was in the template and replaced it with

<b:skin/>

After saving this was automatically updated to:

<b:skin><![CDATA[]]></b:skin>

This will mean that you can no longer use the google "Fonts and Colours" tab in Blogger, but we don't mind as we know all about CSS, right?

Finally, in the template head section, add the link to the favico and CSS file as necessary:

<link href="'http://andpigswillfly.googlepages.com/dave_main.css'" rel="'stylesheet'" type="'text/css'/">
<link href="'http://andpigswillfly.googlepages.com/favico.ico'" rel="'shortcut" type="'image/x-icon'/">

Happy hacking!

Biking

So, I've been learning to ride a motorbike, which is both great fun and absolutely bloody terrifying in equal parts. There's nothing quite like flying down a dual carriageway at 70 mph, clinging on to the handlebars and buffetted by the wind, bumps in the road, passing vehicles and, well, everything really. It's exhilarating!

You really become aware of just how vulnerable you really are - the tiniest slip of concentration, loss of balance, maybe you accidentally let the clutch out too fast, and the bike will jerk and you'll suddenly lose that nice comfortable feeling of stability. Other road users become a constant hazard: one of my instructors once said that when you're driving a car, you look for boxes - car shaped boxes - and your focus is on fitting your own car shaped box in between the other car shaped boxes. A bit like Tetris, really!

Unfortunately bikes don't fit in to this way of thinking, and the sad fact is that most car drivers either have a complete blind spot for bikers, or they don't understand how bikes move and what the biker is thinking. Or maybe they just don't care. Either way, I have the following gratitude to bestow upon the lovely car drivers of Berkshire and Surrey:

• Thanks to the car driver who failed to see me at the spiral roundabout in Oxford - pushing me into the next lane is exactly what I wanted, as I love steering my quarter tonne mass in front of a 4 tonne BMW
• Hooray to the moron in the silver car who cut across lanes without indicating and nearly pushed me off the road
  
• Cheers to the boy racer chav who was racing us in the national speed limit zone, tail gating me at 60 is always fun, and cutting up four bikers in a row is guaranteed to make them happy and cheerful. And thanks to the Mercedes in Oxford for doing - yup, you guessed it - exactly the same thing! A round of applause there, I think.
  
• Hats off to the guy who nearly didn't stop in time when I was waiting for the traffic lights - I'm sure you didn't need me to inch forward into the cycle lane in front, and I'm sure the half foot gap between your front bumber and my rear tyre when you finally did stop wasn't really necessary.
• It's amazing, but somehow the white Transit van on the Tesco roundabout in Oxford completely failed to see the 5 learner motorcyclists in the middle of the road wearing high visibility gear with their lights on, so again - a big thank you to you, special people that you are.

All this has left me with a lot of respect for bikers, as you need to be a much better driver to ride bikes on the road. You need to be super hot on observations, looking not just one or two cars ahead, but as far ahead as you can, and looking at people inside the cars to try and figure out exactly what is going through their heads, as they are protected by several tonnes of steel and crumple zones, and you are protected by probably 1cm of leather and some polystyrene head gear. Yeah, you have crumple zones too - your arms and legs!

Other than that - what a rush! I highly recommend it to anyone - you need a huge pair of balls to commute to work every day, but if you're taking it as leisure, maybe going out on Sunday mornings when the traffic is light, then brillliant!

Highlight of the day: Chocolate spread with bread sticks
Lowlight of the day: Tesco Finest thick cut ham, which tastes exactly like spam
Currently Playing: Tina Dico - Boys and Girls

Portable Connectivity

In my current situation I move around a great deal - I'm on public transport, which means waiting at stations and cafes, and each weekend is usually spent somewhere else, be it at the house, visiting friends, or off to see relatives. I'm also hoping - once the house has sold - to get a motorbike and start touring holidays, and I want to have the flexibility to check my mail, Facebook, do some map browsing, a bit of MSN Messenger, and (perhaps) the odd Skype call. Yes, I'm that geeky (grin).

Realistically, this makes buying a fixed internet connecton kind of pointless - I'm on a 6 month rental contract with a house share for those days when I'm in the Maidenhead area, and pretty much all the internet providers offer a minimum of a 12 month deal, with the optimum savings linked to an 18 month deal.

I also needed a very lightweight, small laptop that I can chuck in a bag - something that's not too bulky, and not too expensive either as I don't believe that tech should be uber-expensive.

As already mentioned, I have a 16MB, 512K Asus EEE laptop - a tiny wee thing with a 7" screen, and it rocks. Here's a full list of kit I'm currently using:

• Asus EEE sub notebook with 8Gb solid state disk, 512K RAM, running Ubuntu 7.10


• Huawei E169G mobile broadband card


• Western Digital 500Gb USB external hard disk


• External DVD-RW drive

Most of this kit works straight out of the box, and is great because it lets me take the bare minumum if I need to, or the works if I want to. The only thing to watch out for is the mobile broadband card - these come with solid state storage so that all the drivers and documentation are on the device itself. What I've found is that everything works OK if you plug this in before you switch the Asus on, but if you plug it when the notebook's running it will get recognised as a storage drive rather than a modem. You can check this by making sure that there are three new USB tty devices created:

/dev/ttyUSB0
/dev/ttyUSB1
/dev/ttyUSB2

Once these are successfully created you can use the Betavine Vodafone driver to set up and manage your connections. This software works regardless of your provider - I've successfully used to to connecto Three (3) and Orange networks.

I think this is a general issue with Huawei devices - I use an E220 with a HP 8510w laptop running Windows for work and I still find things to be a little finnicky with the drivers.

More on peer pressure

I found this humerous clip on MetCafe showing a Candid Camera style test of peer pressure and group behaviour:




Elevator Candid, Must See... - video powered by Metacafe

Multilingual Support

I've added the Google Translator applet to this site, so you can now get a translated version of my blog in your preferred language. No guarantees on the quality of the output though - but hopefully it will be useful to some of you...

General stuff

So I'm finally posting a new blog entry, after a shameful 4 months of inactivity! Practically everything has changed - house, job, lifestyle, outlook...I'm even sitting here blogging on a rather nifty Asus Eee PC (the good one with the 8Gb disk) as opposed to a hulking great desktop....and I have to say it's all so much better :)

I'm now renting instead of owning. I sold my car. I no longer spend countless hours walking around Sainsbury's without really knowing why. Yes, there are things that can be improved, but on the whole it's good.

There's a strange rule in British behaviour that I completely fail to understand, and which is kind of difficult to explain. I call it "pretending to be polite when we really just want to be rude". Here are some examples:

Checkouts
Next time you're at the checkouts and they have one of those long conveyor belts, see how long you can get away without putting one of those "next person" plastic barrier thingies down when there's a long queue behind you. Odds are, you'll be able to make it right the way to the end, and even have all your stuff off the conveyor before the person behind you puts even the first item on.

I've seen this happen a lot (refer to the bit about wandering around Sainsbury's, above) - there seems to be some strange "personal space" rule that kicks in until the barrier is put up, and your typical next-in-line Brit will huff and puff at the end of the checkout and give you evil looks, but won't dare say anything for fear of being rude. Especially if there's a queue behind him. Ironically, most Brits couldn't care less about what you actually do when their shopping's on there just so long as none of your stuff makes it on to the receipt...

Trains
The perfect example of herd behaviour: the train journey. It starts with the crowd at the station - jostling, not too close to be dangerous or appearing over eager, whilst trying to hang back enough to appear nonchalant and disaffected - hey, it's cool, no worries, under control......I'll make it anywa- wait...was that a train I heard? Quick! To the front! Oh...wait....never mind.....Yeah, I was...um...I had leg cramps, OK?

It only takes one person glancing at the clock for everyone to get twitchy, and if you make a positive move, such as standing up and walking to the edge (whilst looking in the direction the train would coming from if it wasn't half an hour away), I almost guarantee that someone else will follow suit. And 50p says that as soon as the train is here everyone's pushing like the world's going to end...

Queueing
I read somewhere that Britain is a nation of queuers. How very concise and apt! In fact, queueing is the natural state of the Brit - whilst standing in line we love to mumble and grumble about "how bloody long have I been standing here" and "wish they'd get a move on", but in a less ordered situation we're completely hopeless. Everything - from supermarkets, to traffic, to the horrendously complicated rules governing getting served at the pub bar - all this involves queueing, and we secretly love it.

There seems to be some unwritten rule that (point point point) he was in front of me, so I can't possibly do anything at all lest a) I offend him or b) break some horrendous social rule, or c) lose my place momentarily and have to start all over again. Yet when someone does push in front, Brits will not, ever, complain (except perhaps in the rougher parts of the country, or in any pub, where you'll most likely be asked who you think you are, if you want to step outside, and whether or not you were staring at "my bird")

And, Finally
For the curious - Eee - yes, it's small. Yes, it runs a little hot. Yes, I nuked the default Xandros distribution and put Ubuntu on there, and it properly kicks arse. I like it :)


More soon....

Disgruntled ranting is justified!

Traffic Jam Mystery Solved By Mathematicians - (via Lockergnome)

Ha! My previous ranting is justified!

:D

Creating secure passwords

The commonly accepted minimum standard for a secure password, and the adopted default security level by Microsoft Active Directory, will be least:

• 8 characters in length
• A mixture of any three of the following: upper case letters, lower case letters, numbers, and non-alphanumeric numbers (e.g. asterisks, exclamation marks and so on)

Inventing a password isn't too difficult - my recommendation is to think of a base word that will be in all your passwords, and then vary it according to the situation that the password is used in, or every time you need to change it. For example:

Fingers1
Fingers2
Fingers3
...etc

Password crackers commonly use dictionaries of words to attack site, so the above example isn't very good, but you get the idea of how a variation can help you create something that's easy to remember. Dictionaries are often based around words found in common English dictionaries, and if your computer has any spyware installed may also contain words that you've entered into your computer, e.g. in search browsers or in documents. Crackers will then use programs to try every one of these words or variations thereof in an attempt to break into your password protected system/file etc.

Most systems that require a password will also check for password likeness - again in the case of Windows, means that your new password can't contain any 3 consecutive characters from your previous password or username. To illustrate, the following change of password would be invalid because both the old password and new password contain the substring "Fin":

Old password: Fingers1
New password: Finding1

You can get around this by substituting numbers for letters that visually look similar. For example:

• 1 is like l or L or I
• 3 is like E
  
• 4 looks a bit like A
• 5 is like S
• 6 is like G
• 7 is like T
• 8 is like B
• 9 like G or g
• 0 like o or O

There are also some non-alphanumeric characters that look a bit like letters too (if you use your imagination!)

• + is like t
• ! and | are like I, L or l
• $ is liks S
• £ is like E
• * is like 0 or O
• ( and [ and <>
• / is like r or R
• @ is like or a
• % is a bit like B

You can even go one step further and combine non-alphanumeric characters to form sequences that look a bit like letters, although it gets a bit abstract at this point:

• |_ looks like L
• /\ and /| and |\ look a bit like A
• |- and -| look a bit like a sideways T (or a small t)
• |= looks a bit like F
  
• \/\/ and \|/ looks like a W
• \/ and |/ and \| looks like V
• |\| looks like N
• |\|\ looks like M
• and so on
  

So, using these three tables of substitutes, our original password (Fingers1) can be changed to any of the following:

Fingers1
F1ng3r51
F1n63/51
|=||\|G3/51

So you see that the simple password that we started off with can quickly and simply become incredibly obscure.