Monday, June 15, 2009

Very Geeky This Time

Well, this was going to be stuff all about Linux, as you'll read, but this came first. For those not into databases, skip down!

I had to improve some procs yesterday and found someone doing all sorts of weird conversions to get dates from datetimes. Hence these from my snippets.

Get Date Only from DateTime
set @date=CONVERT(varchar(8), GETDATE(), 112)
Get Time Only from DateTime
select convert(char, getdate(), 108) --hh:mm:ss
select convert(char, getdate(), 114) --hh:mm:ss:mmm(24hr)
Looking for Columns in all the Wrong Places
Looking for where a field is kept all through the tables in your database isn't much fun. This will look for and list all the columns in all the tables in your database.
SELECT table_name =,
column_name =,
datatype =,
length = syscolumns.length
FROM sysobjects
JOIN syscolumns ON =
JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.xtype='U'
-- and like '%[known column name]%'
Order the full listing by, syscolumns.colid instead, and you'll group all the field names by table.
Hi ! Welcome to the next alternative personality in this blog! I'm the person(a) who chose DOS unstead of SOS, because of the new PC's ability to pack a FORTRAN compiler. "Whaaaat?" you splutter in amazement - "What are you raving about?" ... but it's true. I had to choose between a PC (expensive, looked like a lab instrument) and an Apple III (expensive, cool, sleek, white). The PC ran MS DOS 2.2 and the Apple ran SOS (Sara's OS !). The Apple ran lots of cool games, but I didn't play games. I needed a PC that I could use in work. A computer that I could write programs on. One, in short, that had a FORTRAN compiler. The Zenith PC that I eventually settled on offered a choice of three OSs, although I chose DOS. It arrived with about a foot of manuals (8" binders, well packed with information on all the extra utilities on the extra disks (5.25" floppies) over and above the basic MS DOS disks. The extras, in fact, very much resembled a lot of the utilities available in contemporary UNIX systems.

Since then I've gone through almost the whole evolution of Microsoft and IBM-compatibility. I bought the Zenith because I wanted a better monitor than IBM offered. I upgraded the 10MB disk by adding a 20 MB one within 6 months, and by the time the Zenith was a year old I'd replaced the CPU with a faster Zilog model - 8 MHz instead of 4.77 !

After a while I sold the Zenith and moved on to a 286 machine, also a Zenith, as it happens, and then to a 33 MHz 386 with no less than 16 MB of memory (all that would fit on the motherboard!). All this time I was able to use the machines - running versions of Windows after 1985 - to write code in FORTRAN and ObjectPAL programs. That's a pic of Windows 1.1 on the right. Windows wasn't an OS (well, neither was MS DOS, really !) but it was a program that managed the screen. At this point MS had lost a legal battle with Apple and couldn't have overlapping panes in Windows - tiling only ! Windows had colour, though, and the Lisa was only monochrome. The Mac arrived about a year before Windows, and was certainly an object of jealousy!
I pretty well skipped the Windows 9x route, moving from Windows 3 to Windows NT 4, a smart move, which led to a brief sojourn in Win2K and almost 10 years now with Windows XP and XP/64.

The point of this little gambol through ancient history has been to show you that I had a serious choice to make back in 1981 about which machine to buy. The Apple was (to use Top Gear terminology) lithe, swift, and sexy. The IBM Compatibles looked like metal boxes spray-painted with colour dyes designed for tinting cardboard cartons - beige or beige! In fact, that's what they were!

However, for me, I chose the one that I could work on.

Now I'm looking at the same potential choice. I've been running Windows XP and been ignorantly happy. I've looked at Vista and decided that unconstrained software growth is not the way to go. Three years later Microsoft publicly agreed with me. Windows 7 offers certain extra things for upgrading, but they're mostly for the "average user" or the games-kiddy. I'm not inteested in glitz for glitz' sake, but offer me a good price on a faster graphics card and I'm all ears. I'm the kind of guy who runs his PC logged in as admin, who has to buy special gadgets to cool his laptop, and runs a full-out database system (MS SQL Server 2005) and a development system (MS Visual Studio 2005) on his laptop. Pretty isn't really "me".

My choice, as I saw it, for the OS of my next PC, was between Windows XP or a flavour of Linux. Win XP because, having seen Vista, I never wanted to see it ever again! Windows 7 wasn't - and still isn't - an available possibility. The alternative was Linux ... but which one? Red Hat ? SUSE ? Solaris ? BSD ? Mandriva ? PCLinuxOS ? Mephis ? Zenwalk ? In the end I settled for experimenting with Ubuntu 8.10. Mainly because I found two or three (English) Linux magazines and they seriously praised Ubuntu. I also picked up a CD of version 3 of the suite ("OOo") off another magazine.

My first test was to install Linux on a small PC - a very basic HP 740n. When I say "basic" I mean a 1.5 GHz cpu but only 256 MB of RAM (yes! more than 256 times more than my first
Zenith ever had !). I popped the distribution CD into the CD ROM drive, booted the machine, and told it to take the whole disk and make it Linux. Which it duly did. So my son suddenly had a PC but with a new and different face.

It didn't take him long to get accustomed to this, thought, and he's even done some of his school homework on it. The stuff has to be handed in in MS formats - Word, Excel, and Powerpoint - but that's no problem at all with OOo. Not something I could ever really say about WordPerfect Office, or the Lotus office with AmiPro, nice as AmiPro was.

About a month or so ago I wanted to tweak something on that machine and managed to mung the GUI. Totally! Had I been a Luinux guru I could have fixed it. But I wasn't - and still am not. Now I could have re-installed it, and, had I been using Windows, I would have had to. However, Ubuntu had moved on in the meantime and I installed Ubuntu 9.04. Now this is a definite improvement on 8.10. It's noticeably faster and will surely get better still when I finally get around to upping the memory to max out the old HP. In the meantime, Nick has a very serviceable PC that he can even play space and racing car games on. And they don't slow down intolerably because of the lack of memory, believe it or not. Nick "got" pretty much everything about the new OS the first time I explained it, and he isn't any more "PC Savvy" than any other game-infatuated 12-year-old. Within days he was downloading and installing programs as if he'd been doing it for years.

That's what really convinced me that Ubuntu was very possibly the way forward. I can obtain development systems for many languages on any system these days, but, for a system with such a small user base (compared to that of Windows), Ubuntu help and support on-line is stellar. From meeting VB at the stage of VB.NET 2.0 I can tell you from hard experience that good on-line help is utterly invaluable.

So, my next machine may well be a little ASUS desktop, but, whatever it comes with, it's ending up with Ubuntu. No question about it.
After the SQL into, I have to break the news that I'm using up all my spare time learning a new language. No - not Spanish or Italian! It sounds a little Potteresque to those not used to computing, but the name of the language I'm learning now is called Python. It's rather a cool language and, in a few days time, I should be able to put down the crutches and start hobbling in it.

Out in the real world, the AG finished an awesome witches hat - all spiky and obviously great for making creepy silhouettes while riding your broom across a full moon. She's also made a woollen tricorn hat (which everyone insists on calling a Pirate Hat). Pictures of these to come. She also finished and sent a pair of Tiger Socks to my dad in England, meaning that three generations of Irwins can now wear AG-created Tiger Socks of their very own !

Tuesday, June 09, 2009

SQL & Socks

Firstly, the techie stuff (as I'm also watching Star Trek) and woolly stuff later on down.

Geek Stuff

I've been troubled with heat, slow speed, program lockups, and general vanishing hard-disk space on my laptop. I run MS SQL Server 2005 and VS 2005 on this machine for development, and, more and more often, running both intrfaces has locked them, with one or the other usurping the processor, taking 97%+ of cpu time.

This evening I was checking disk ocupancy to try to discover why I was running out of space. I was using a copy of FolderSizes, and found that at some time in the past I had been experimenting with scheduling backups for a database, and obviously never turned it off. Now turned off, I've removed several GB of backup files and life is better.

Something else I saw was that the log file from one database, itself 2 GB in size, had increased to about 25 GB, causing my system to run short. Truncating the log file got it back down to 2 MB! Here's the code:

use DataBaseName

backup log
DataBaseName with truncate_only

dbcc shrinkfile (
DataBaseName_log, 1)

This is definitely a wake-up call for all of you small-system DBAs: SQL Server documentation is quite clear that the database retains the log data until you clear it out. What the docs cannot tell you is how big it will get nor how quickly it will grow - that is totally up to your application. I managed to use up 25GB because I started with 2 GB of data and spent about a month doing analyses on it. Pretty soon I'll be able to get rid of the original database itself - phew!


Here's something nice to eat. It should take you about 30 - 45 minutes from the time you enter the kitchen to the time you're mobbed as you sit to eat. It's called Mike's Bacon-wrapped Chicken, and I serve it with corn and couscous. I'm not going to be absolute with quantities - obviously you'll need more for more people.

Firstly, get all your ducks (chickens ?) in a row - this'll make everything go lots quicker, and get you to the table sooner. You'll need a sharp paring knife, tongs (like you might use barbecuing), but not huge, a cooking spatula and spoon. To cook in, a frying pan and two cooking pots. You'll also want 2 or, maybe, 3 bowls to put cut veggies into. Determine what people will want to drink before-hand and arrange someone to handle that for you.


Start with a smallish onion. Peel it and chop it in to pieces about two tenths of an inch in size. Put them into a bowl and reserve. Add about three or four crushed and chopped cloves of garlic to the onion. Chop up one or two peppers (any colour, but one of each is best). Make sure you dump the central area around the stalk, and all the seeds. Reserve them into a bowl (or two, if needed). Get out Rosemary, Thyme, Salt, Pepper, and (optionally) Nutmeg.

Put water into one pot and frozen corn too: put the heat under that to medium. Put some butter into the other pot - again dependent on how many people / how much couscous. You'll need almost a cup of couscous per person, together with chicken broth for the couscous and either white wine or more chick broth for the main dish

Take a chicken breast per person, wash, and dry them (just in case there's bone fragments from butchery). Trim the extra fat off each and cut each breast into roughly 3 or 4 equal-sized pieces. More important than being perfectly equal is that you cut the really thick parts of the breast parallel to the table to try to even up the cooking times for all the pieces. Finally, you'll need some wooden cocktail sticks - about 3 or 4 per chicken breast.

Take out one strip of bacon per piece of chicken but keep it aside with the cocktail sticks.


Start medium-high heat under the frying pan and put some cooking oil in to warm. Personally, I use Safflower as it has a high smoke-point, so things get brown nicely, and it also doesn't taste the food much at all. Get the oil reasonably hot and put in the onion and garlic mix in to start cooking.

Once the onion is beginning to look cooked - about a minute or two - add in some salt and pepper, stir up to mix, and then start adding in the chicken. Don't worry if it doesn't all fit in - two stages will be fine. Raise the heat at this point and fry off the chicken for about two - three minutes a side. We want the chicken to be browning now, but not cooking in the middle.

Once the pieces of chicken are browned, take them out (tongs!) and put them on a plate to cool. Put in any that are still waiting and cook them. As soon as the chicken is all done, replace it with the peppers, some Rosemary, Thyme, and Nutmeg, and lower the heat to let them fry gently in the onion and chicken juice.

Meanwhile, take each piece of chicken, wrap a rasher of bacon around it, and pin the bacon on with a cocktail stick. Store in one of the bowls used for the veggies now in the pan.

As soon as the chicken is wrapped, return it all to the pan or, of it won't all fit, get another pot and transfer some of the veggies there, follow with the chicken, and add the rest of the veggies. Not add wine or broth. If in the pan, then about half-way up the pieces of chicken; if in a pot then until you just begin to see the liquid level. Either way, as soon as the liquid is in you should raise the heat to bring it just to the boil before lowering it again to a simmer. We can best keep this simmering for about five minutes or more.

Next, raise the heat on the corn to bring that to the boil before letting it just keep warm.

Now the couscous. This is a starch that cooks incredily quickly, so we can afford to keep it until the very last few minutes. You'll find the butter in the pot: it should be melted by now. Raise the heat to about medium-hot and add the couscous grain. Use a spoon to keep stirring the grain as it gets coated with the butter and starts gaining temperature. If you listen (yes, listen!) to the pot closely you'll be able to hear it start to hiss as it starts to fry. This is fine for a while, but eventually the couscous will start to brown (from a golden-yellow to a brick-red) and tat that stage it gets bitter, so you don't want to go too far!

Once the couscous is hot you can pour in the chicken broth. Start with a cup for two of couscous - as it hits the bottom of the pot there should be a kind of a "whoomph" sound as the broth turns to steam. This is good, as it pushes liquid all through the grain, cooling it quickly. Put enough broth in that all the couscous is in a very slightly soggy mass. Don't be scared to put in too much broth - too little leave "raw" grains with will seem like grit. Take the couscous pot off the heat and continue to stir quite quickly. It will absorb the liquid: if it looks to be too dry then add some more!

Now you are happy, leave it off-heat for a few minutes and turn off the corn, pour it through a sieve, and put into a bowl to serve.

Next, use your tongs to take out the bacon-wrapped chicken from the pot or pan into a serving bowl and raise the temperature under the rest to max. Add more wine or broth if needed: we want a kind of gravy here with lots of bits, so it needs to reduce for a minute or so: to do that it also needs something to reduce from !

While the veggies / broth mix is boiling, take a fork and stir up and finally fluff up the couscous. Pour it into a bowl to serve too: and fluff it up again now. Aerating the grain will let steam percolate better and make sure that grains still in need of moisture can get it.

Now everything else is ready for the table, kill the heat under the veggie-liquid and pour that into a bowl. Bring everything to the dining table to serve.

That may seem like a lot to do, but in all honesty it takes me about 30 minutes to make enough to feed four and have enough for four more little lunches to take to work. Don't expect to do it that fast to begin with - in fact, get someone to read out the instructions to you as you go, to save stopping and finding your place every few seconds.


It was the spawn's birthday last week, so among other things, he got a pair of wool short-socks. Well, he will do, when the AG finishes the second one!

We went to Woolbearers last week, so there are three balls of black yarn waiting to go into the AG's Witches Hat for Hallow'een. Tomorrow it's the Grindhouse Café in Haddonfield for Stitch 'n' Bitch. If you're there, come and meet us all.