Rob Farley

Itzik Ben-Gan posted a challenge to his blog and to the SQL MVP newsgroup. The challenge was to be able to use T-SQL to create palindromes from a list of words. The words would have to be unique, so that the list wasn't endless.

A handful of MVPs (including myself) pushed out potential solutions. All slightly different of course, because of the creativity possible within T-SQL. Itzik posted the results at http://www.sqlmag.com/Article/ArticleID/95008/sql_server_95008.html and it turns out my query was the fastest. Go figure! I'm not sure that Adam (Machanic)  or Steve (Kass) tried to optimise theirs much. I imagine if we all tried to find ways to improve our queries, they could all become a bit faster again.

The key thing with something like palindromes is to build them up as you go, looking for candidate words that can continue the sequences from the start and end - and this makes them a great candidate for CTEs. I also wonder if you could easily build them up from the middle, but that would then mean keeping a track of where the middle was... and I'm not sure it would end up as fast. Maybe another day.

I was on a plane recently and saw the new Helen Mirren movie The Queen. It was a well-done film, although in many ways it was just a curiosity about how they would show the events, rather than wondering what would happen next.

One thing got me though... I thought at first that there was an occasional problem with the sound, but on the third time I realised what was happening there's a line when someone says "It was a vow before ... " (I've put the dots where the sound cut out). And I realised that they were cutting the sound every time someone referred to God.

Considering that the Borat movie was also showing on the plane, it does make you wonder what the definition of 'offensive' is these days. And will the text used at the coronation of George VII be altered so that he no longer makes a vow before God, and isn't made the head of the Anglican church?

Seems I have two new Microsoft Technology Specialist certifications. MCTS: Microsoft Windows SharePoint Services 3.0: Configuration and MCTS: Microsoft Office SharePoint Server 2007: Configuration. The links are to the exams I passed, not the certifications themselves. Seems the cert sites don't quite exist yet.

I sat the exams in November, during the beta period. I got the results in the past week. Seems these two exams each earn you a MCTS certification.

I'm sure I've written about this before, but it comes up so regularly in the newsgroups that I thought I'd write an answer here.

A table (probably an audit table) exists with a field that stores a date signifying when a change was made. You need to return a result-set which has a row for each period of time between modifications.

For example:

CREATE TABLE QtyAdj ( .... , ModDate DATETIME, Qty INT )

--where I haven't listed all the columns - there should obviously be a primary key on this. In fact, I'll assume there is a field called ID which is unique.

What we want is:

with modnums as (select *, row_number() over (order by moddate, id) as rn from qtyadj)
select m_this.moddate, m_next.moddate, m_this.qty
from
  modnums m_this
  left join
  modnums m_next
    on m_next.rn = m_this.rn + 1

Which uses row_number() within a table expression to give a unique number to each row ordered by the date. You can then join between two copies of the table expression (love CTEs for that), to get your consecutive records in a single row of your result-set.

Last week I presented at my user-group about PowerShell and why every DBA should know this. The talk went for just over an hour, and as most of the audience hadn't used PowerShell at all, I started from the top and really pushed concepts like "You pipe objects not text". The script can be downloaded from here.

So then by the time I got around to talking about the fact that you can really easily hook into ADO and SMO, I think the audience were already caught on the idea that PowerShell really is very powerful and that anything you can do with SMO can be done really easily with PowerShell too. Most DBAs write scripts that use SMO to manage their SQL boxes. In the past they've used VBScript, but I think they should use PowerShell - it's got so much more going for it, and because it can replace cmd, there's almost no reason not to use it.

The talk didn't go into all the stuff you can do with SMO - that's a different presentation. This was a way of demonstrating that you can use PowerShell for SMO, as well as everything else you might want from a sysadmin perspective.

Someone suggested I check out the Acer Ferraris. Yeah, seriously. So I checked out the specs. Frustratingly, Acer don't have them on the their list of products in Australia. The UK Acer site seems to suggest that the F1000 is actually quite well priced (although UK prices on hardware are often quite different to Australian prices).

But if Acer aren't selling them here, then that would make it a little awkward, don't you think...

What would you recommend? I want something that will happily run virtual machines without blinking, so that it can be an effective development environment as well as running all the server products I'd care to code against (SQL, WSS, etc). It has to be as good as it needs to be when I present at user-groups or train people.

The Dell D820 seems to be quite popular. HP's nw8440 seems okay, but maybe overpriced? Sony Vaios seem to be much lighter, but is that worth the extra expense? And what about IBM?

Love to read your comments... So far I'm leaning towards the Dell.

Of course the idea is the same as Unix's AALib, but what's so cool is how little code it takes to do this in PowerShell.

The idea is simply "Load up an image in System.Drawing, work out the brightness of each range of pixels, and from that, give it a character to represent the block of pixels."

So this is me:

So I guess the idea is to find cool things to do with PowerShell. AALib is a very cool idea, and that makes this code very cool too.

Whooiz is changing. They have a new logo, they're dropping the capital H (but it might take a bit of time for this one to go through), and the widgets are now much more dynamic.

So in my blog (if you're reading this from elsewhere, go to http://msmvps.com/blogs/robfarley), I now have a much smaller 'whooiz friendz' section, and I only show one friend. It's not that I have offended everyone and only have one friend, because if you watch for a few seconds, you'll see the friend change to a different (random) friend.

This is really nice. It's something I was tempted to do myself before, but Clarke beat me to it (it's fair enough, he's the guy behind Whooiz). I told him I was going to do it, but just didn't get the time over the holidays to scrape through his script for the bits I wanted to change.

Unfortunately it now lives within an iframe, which gives a little less control over the CSS. My trick of setting the header to not display no longer works, but instead, you can now put '&noheader=y' on the end of the querystring, and that achieves the same (or at least, it will once Clarke has made the change for this).

The code I'm using is:

<div class="sideNavItems"><h3>Whooiz Friendz</h3>
<iframe id="Iframe1" frameborder="0" scrolling="no" height="200px" width="100%" src="http://whooiz.com/FriendsList.aspx?Profile=56&type=0&bg=ffffff&hrd=000000&fn=1&noheader=y"></iframe>
</div>

The 100% lets it fit nicely in the div tag, and 200px is what I reckon makes it fit nicely, although it could probably be adjusted down a little more.

Now, presumably this could be thrown really easily into a sidebar gadget... 

Because I've been playing with PowerShell more and more recently, I'm going to do a talk at my user-group about it next week. Should be a lot of fun, and a great excuse to get deeper into the technology over the weekend.

One thing that I think is really great is the ease with which you can apply things to a list, because of the pipe operator - which works just like the pipe operator in any other kind of shell, but actually handles the things as objects. In my example here, it picks up the fact that I'm passing in a list ($servers), so that I can then do a 'foreach' loop (using the % operator). I put it all in a process section so that the $sql command drops out of scope, releasing the object nicely (which makes it equivalent to setting it to nothing). I could also used Remove-Variable 'sql' to drop the reference, or just set $sql to something else. But I prefer to let it fall out of scope - it just feels a bit neater.

Please forgive the cases, this is just as I typed it...

$servers = 'server1','server2','server3'
& {
  $servers | % {
    $sql = New-Object ('Microsoft.sqlserver.management.smo.server') $_
    $sql.Databases | Select-Object @{Name = 'server';Expression = {$sql.name}}, name, lastbackupdate
  }
}

The @{Name=;Expression=} bit lets me put something into the table which doesn't come from $sql.Databases, and hey presto, I have a nice table come out telling me when the databases were last backed up. I could easily push this through a Where-Object filter to only get ones that haven't been backed up for some time too.

Incidentally - you should run

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

first to make this work. I'm just so used to having this load up as part of my PowerShell profile these days... 

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.

England have let Warne be Australia's top batsman in his final test. Very nice of us. And just to demonstrate that it was charity, we got him out less than ten runs after he beat the next best Aussie.

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 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.

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).

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!

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.

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("http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;wrappertype=mainframe")
$titlestart = [Regex]::Matches($ret,"<title>","IgnoreCase")[0].Index
$titleend = [Regex]::Matches($ret,"</title>","IgnoreCase")[0].Index
$ret.Substring($titlestart+7,$titleend-$titlestart-7)

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("http://content-aus.cricinfo.com/ausveng/engine/current/match/249226.html?view=live;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.

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).