How to attach your MVP status to a country without making your address public

I showed this to Darren today, because he wasn't coming up in the list of Aussie SQL MVPs.

Within the profile section, restrict your address to be just your capital city, state and country. Make it public. Put your proper address in the shipping address section, and make it private to Microsoft. That way, you'll still receive all the MVP correspondence properly, but when the greater public searches for you, you'll be listed happily with the people that live in the same place as you.

I can't guarantee this is fool-proof, but since Microsoft only ever uses your shipping address, it ought to work just fine.

Community Server and Feedburner

The discussion about knowing how many people read your blog was started the other day by James Green. I quite like the idea of waiting for people to tell me they've read my blog, or referring to it in their own blogs, or posting a comment on my own one. But feedburner can be useful too.

If you're using Community Server as your blog engine (as I am), there's a trick to this. First, you need to go to communityserver_feedburner the Syndication Settings page and look to see what the private RSS feed is. This is the one you tell feedburner to use. Then you should tell Community Server that you're using an external RSS feed. On this same Syndication Settings page, you tell it your feedburner feed. Then anyone who's subscribed to your normal feed will be redirected to the feedburner one, which in turn will redirect to the real one. No-one should really notice, except that you'll start to have feedburner record that extra bit of information for you.

But it still doesn't help tell you how many people read your blog through other aggregators, like TechTalkBlogs (which I really like, and use, but then people like James Green don't see me as a subscriber).

And why is it actually quite important to have an idea about if people are reading your blog or not? Well, if you feel like your audience is a worthwhile one, then your quality of posts will improve, which will improve your audience, and soon you might be getting a laptop. Umm… I mean, soon you might find that you are a better writer, with a higher profile, more confident about your views, and generally stronger in your field.

Finding old friends

It's great to discover old friend's blogs. Today I've come across an ex-colleague and good friend Matt Taylor-Coffey. When I knew him he was Matt Coffey, but he married Beverly Taylor and got an extra name. Now I think he's dropping the Coffey and becoming Matt Taylor. So says his blog anyway.

I used to catch up with Matt on Msgr quite a bit, but not for some time now. Matt's a big ManUtd fan (even gave his son the middle name of "Trafford"), and we'd often talk about the beautiful game. Now he's an expert in SharePoint and more and more involved in the SharePoint community around London.

Great to find his blog – it means I'll feel like I'm in touch, even if we're not actually communicating on an individual basis. I just hope he gets back onto Msgr now and then (time-zone permitting).

Removing all the rows bar one per customer from a table

I stumbled upon James Green's post about T-SQL and couldn't help but post about it. It's not that I want to blog about his blog, but rather I want to jump up and down about the fact that he's missing one of the best things about SQL2005 – the OVER() clause!

James' task is to remove all the rows from a table, except the lowest number order for each customer.

He has written a query to generate code to do this, and then copies the results into another query which he then executes. All well and good as an exercise is code generation, but I just want him to use:

with CTE_OC as
(select *, row_number() over (partition by customer_id order by customer_id, order_id) as rn from OrderCustomer)
delete from CTE_OC where rn > 1

Which does the whole thing in one step. The row_number() bit gives a number to each row, starting again at one for each new customer. So then you just delete all the ones that aren't the first one for each customer. Easy, James!

Who reads your blog?

James Green is wondering how to tell if no-one reads your blog. I'm not sure the answer is to use feedburner, because that doesn't account for people who read your blog through other aggregators.

I don't think the answer is to only publish excerpts. That only works to annoy people who read blogs when they're offline (like me!)

But perhaps the best way of doing it is to encourage comments on your blog. Do you read my blog? Don't answer 'no', please… I won't believe you.

Cricket PowerGadget

I was thinking about PowerShell and how you can get it to do fantastic things. And I wondered how easily it could be used for scraping cricket scores.

So I threw together four lines of code to grab the cricket scoreboard from cricinfo and rip out the title.

$ret = (new-object Net.WebClient).DownloadString(";wrappertype=mainframe")
$titlestart = [Regex]::Matches($ret,"<title>","IgnoreCase")[0].Index
$titleend = [Regex]::Matches($ret,"</title>","IgnoreCase")[0].Index

Edited: This can be done easily in one line – Lars pointed out the use of Regex to grab the section between the title tags, which then means we don't need to store $ret at all. It can now be:

[Regex]::Match((new-object Net.WebClient).DownloadString(";wrappertype=mainframe"),"<title>(.*)</title>","IgnoreCase").Groups[1].Value

It's not particularly elegant, but it works nicely. I would've liked to have handled the HTML as XML instead, and just gone straight to the Title tag, but there's stuff in there that won't convert to XML, so I guess that option wasn't available.

And the really nice thing about this is that I can put these four lines into PowerGadgets, and in all of 10 seconds have a floating gadget which I can use in XP as well as Vista, and (in Vista) put in the sidebar if I want. I've told it to refresh every minute, which won't refresh as quick as some, but hopefully won't stop working too quickly. It's not quite as nifty as Darren Neimke's gadget, but then again, this was really really quick to throw together.

And of course, I've left the advert for Cricinfo in there. I wouldn't want to hide the source of the information. And if they ask me not to do this, then of course I'll stop. Cricinfo have a great site, and I really don't want to upset them.


Five things

Nick tagged me. Thanks Nick. Turns out you are still here. Hmm… Five things about me you might not know.

1/ I'm a Christian (and proud of it). I have been all my life, and don't remember not ever being a Christian. I actually come from a long line of Christians, and 'minister' is one of the more common career choices in my ancestry.

2/ My grandfather is a personal hero of mine – he's a past-president of the Royal Aeronautical Society, and was Engineer of the Year in 1984, I think partly because of a few helpful passing comments he made to NASA regarding some of the problems they were having with the shuttle.

3/ Most of my high-school years were spent at a school called Habs just north of London (I figure most people know I'm actually English and that doesn't qualify as something you don't know), where Matt Lucas was in my year and Sacha Baron-Cohen was a couple of years ahead. It was my father's high-school too, but he died when I turned ten and never saw me go there. Neither of us appear on the Wikipedia entry about the school.

4/ I only have one GCSE, an A in Maths. Most people have lots, but I only have one, because of the timing of our emigration to Australia. But I do have three bachelor's degrees, which mainly comes from starting with a double degree and then doing honours in something else.

5/ The last few years have been somewhat hospital-centric for my family. I had a back injury in 2003 and spent a while in hospital learning to walk again, and in 2004 my son Samuel (who was seven at the time) got Kawasaki Disease. In many ways, both experiences shaped us more than we would have liked, but we're both still here and doing well.

Now I need to pick five other people to tag (who haven't already been tagged by others). In no particular order…

Geoff Orr (good friend in Sydney, knows SQL), Brian Madsen (ASP.Net MVP in Perth, runs the SQL UG there), Glenn Block (works for Microsoft Learning in Redmond), David Gardiner (member of my SQL UG in Adelaide) and Joel K. Furr (the inventor of the word 'spam' – lives in the US state of Vermont).