Monthly Archives: October 2011

I love half term, I love half term, I just love half term so very, very, very much

Half term is a lovely time for cycle commuting.
The busy roads are (nearly) free flowing, cars are not running so late because there’s not as much traffic, so they tend to drive better too.

Normally the main road is jam packed with traffic well before I turn off, so I normally take the hatched area in the middle until my turning. But this morning I took the main road route rather than cutting through the backroads as I normally do because there wasn’t queuing traffic or anything slowing me up. I cruised down the smooth tarmac all the way without being held up by cars at all, it was a really good commute today, I wish every week were half term week.

SQL Server: Create and drop a temporary table

The following shows a basic example of how to create a temporary table, insert some data and select the data out:
(IsSomething BIT,
IntValue BIGINT)

INSERT INTO #TEMPTBL (IsSomething, IntValue) VALUES (1, 1234)


When scripting with temporary tables it’s more difficult to figure out if the table exists or not (i.e. you can’t use the normal mechanism), so I simply wrap the drop table command in a try catch block and ignore any errors:


XPath to return non-empty values

This is an example of an XPath I’ve used before to get the nodes with a non-empty attribute:
// Where rows have a non-empty subtag called field with a name attribute equal to attrib1, select the rows
XmlNodeList nodeList = xmlDocument.SelectNodes("./idc:row[./idc:field[@name='attrib1'][text()]]", xmlnsmgr);

Note: the key part in this XPath is the text() part.

I’m not totally convinced this is the correct XPath to be using.
Of course there is also the node() function.
Combining these with not (e.g. not(node())) may yield the correct way to do it.

For further information try:
W3C XML Path Language (XPath) Spec
MSDN XmlNode.SelectNodes Method

Light comparison and beam shots

Now is the time to review your lighting system, save, beg, plead and write to father christmas.
I’ve tried year after year:

but all I got was socks and of course I need more socks don’t I?

I’ve always liked Wiggle, but I love this: light comparison and beam shots
More cycle shops should do this and then more people would realise a £30 light is not sufficient; to be honest I would spend more on a light than most would on their bike.
My eye was on the Exposure Joystick Mk6

which seems to have a nice beam pattern for road use

but I probably wouldn’t want to spend the money also there are less favourable beam shots, for example on
My advice is to look around at reviews on the internet first, some will be more favourable than others, but is a good place for checking out bike light reviews as they normally include video and lots of photos. Remember though that photos are only good for comparison if taken using same camera settings, so best to only compare images on the same site.

The imported lights such as the Magicshine brand offer impressive output at a much lower cost, see this candlepower forum thread for details.

For comparison, my three year old AirBike Everlight SL1 (SSC P7) cost £120 new, see this candlepower forum thread for details and lovely beam shots.

Since I’ve also just linked to candlepower forums, worth mentioning that site is a good source of light information, but a little less formal than mtbr.

Oil change and tiny service

Thursday and Friday my chain popped off once on the way to work and once on the way back. This is a sure sign I needed to tighten it, so I planned to give my bike a tiny service of sorts today.

My chain was jam-packed with grit and muck, more so than usual, so it’s another sign it was due.
Well, to add to this, checking my records, I was a few miles past oil change schedule so I thought I’d better do that too.

Clean chain – Spare rags, WD-40 and a broken spoke to clean. Finish Line Cross Country Wet Chain Lube to re-lubricate. Sometimes I use a chain cleaning device, but then you have to clean that, wash the chain in water to get the citrus degreaser off, use WD-40 to (attempt to) drive the water out then re-lubricate. Other times (like today) I just use the WD-40 and rags to clean the chain with the broken spoke to poke out the hard to reach chunks of filth. The chain cleaning device works better but always seems a bit more of a faff.

Tighten chain – Loosen eccentric bottom bracket bolts, rotate bottom bracket, tighten bolts again. It is important to have some play in the chain, leaving the chain tight will cause extra stress on the internals of the hub and in theory cause premature wear.

Rohloff oil change – Since winter is almost upon us I used about 15ml Rohloff oil and about 7ml Rohloff cleaning oil.

Checked brakes – I’ve kept them tuned up, so I was checking the state of the pads, they are looking worn still, no surprise there, I’ll change the front ones next time I think. I picked out a small amount of aluminium from the front pads.

As per usual I thought about cleaning the frame and wheels, but it’d only be dirty again by Friday so I didn’t bother.

SQL Server: Replace Carriage Return and / or Line Feed characters

Use the following to replace all Carriage Return and Line Feed characters with spaces

replace(replace(string, char(10), char(32)), char(13), char(32))

10 = Line Feed
13 = Carriage Return
32 = space

Normally you will want to trim the resulting string too:


SQL Server: Querying XML fields – XPath, XQuery

If you can remember the syntax you are better than me, but here is an example I crafted for querying XML in SQL Server:

T.X.query('declare namespace N="http://helpmenow";//N:me[@now="false"]').value('.', 'NVARCHAR(MAX)')
CONVERT(XML, '<help xmlns="http://helpmenow"><me now="true">yay</me><me now="false">too bad</me></help>') X
) T

Of course the result will be “too bad”.
Once you have the value of a field like that it is a normal string, so could be used in a where criteria etc.

See also MSDN – XML Support in Microsoft SQL Server 2005