Rob Farley

Darryl Burling’s podcast

Rob Farley — Tue, 03 Nov 2009 04:09:03 GMT

Seems strange to write about this, as NZ Product Manager for SharePoint, SQL Server, BizTalk and Visual Studio Darryl Burling has just announced that he’s leaving the SQL, BizTalk and Visual Studio products, replacing them with Exchange Server and Microsoft Online Services instead.

A few weeks ago he started a podcast about the areas that interest him, and has done shows interesting shows about SharePoint, SQL and BizTalk so far. He’s even done one about Office Online, which I haven’t checked out yet.

Darryl comes from a technical background, having moved to the Product Manager role out of the Developer Evangelism team, and was an MVP before joining Microsoft. I think his podcasts reflect this, and it makes them worth a listen.

The various links to subscribe are on his website, http://burling.co.nz

Non-Administrators couldn’t log in, and Admins couldn’t Run As Admin

Rob Farley — Sat, 24 Oct 2009 04:28:14 GMT

One of the Windows 7 machines in my house had a problem recently. Non-admins couldn’t log in, and there was a message saying that the System Event Notification Service wasn’t running. I could log in as admin, but couldn’t escalate to run things as Administrator. The error “The system could not find the environment option that was entered” would appear.

The standard web searches weren’t my friend, but I eventually found that if I started in Safe Mode, I could run a Command Prompt as Administrator and disable UAC. Then I could log in as me (with admin privileges), and run setup.exe from my Windows 7 disk and do an Upgrade to repair my system.

http://superuser.com/questions/58137/system-event-notification-service-doesnt-start-cant-escalate-to-admin

Book review: Windows 7 Inside Out

Rob Farley — Sat, 24 Oct 2009 04:08:09 GMT

Perhaps it’s because I keep finding new things about Windows 7 that I really like... Today’s one was that you can drag the top (or bottom) edge of a window to the edge of a screen and have that window fill the screen vertically, but keeping the left and right edges still (great for when you don’t want it maximised, or docked to the left or right). Another favourite of mine is that you can Shift+Right-click on a file and see “Copy as Path”, which is great when you want to paste the full path into a textbox, or PowerShell, etc. I don’t know if this was available in Vista, but I’ve only noticed it since I’ve been using Windows 7.

…but anyway, perhaps it’s that, but I thought it might be worthwhile to pick up a book about Windows 7. I ordered Windows 7 Inside Out (by Ed Bott), and it arrived this week. It’s not short (about 1000 pages), but I flew through it. I guess because I’m already an experienced Windows user, I skimmed a lot of pages. A lot of the features discussed aren’t new, but Windows is such a large technology that it’s good to look through a good reference book, and with Windows 7 having a lot of new features, it was great to be able to go through this book looking for nuggets that I didn’t know.

And there are plenty of things that I’m just not that interested in. There are sections in this book about IE8, Windows Media Center and Windows Live. It’s not why I picked up the book. They’ll be useful for anyone who borrows the book from me, but I ended up skipping those sections almost completely. I’ll end up going back to them one day maybe. The book comes with an eBook version, which I’ll stick onto my phone and load up when I need it.

Ed Bott (the author who I doubt has a brother that shares my given name) is a journalist, and it shows in his writing. I found it easy to read, and the book is full of tip sections.

So yes, I picked up the tip about dragging the top border from the book. But I found the Copy as Path option myself. Nowadays, I hold Shift down whenever I right-click, just in case there’s an extra option in there I’ll like.

Book review: Programming Dynamics CRM 4.0

Rob Farley — Mon, 19 Oct 2009 07:04:06 GMT

I don’t consider myself really in the Dynamics CRM space. My area is SQL Server. Currently I’m spending most of my time in the Business Intelligence space, with plenty of stuff with relational databases as well. But that didn’t stop me from picking up a CRM book recently.

Jim Steiger’s Programming Dynamics CRM 4.0 was the book in question, and I was pleasantly surprised. It seems to be very comprehensive, and well written as well.

I can’t say that I tried all the examples – that would be lying, but I do feel that I have a much better understanding of what’s involved in programming against CRM, and that I have a really good resource available in this book.

I do keep wondering about how much I’m allowed to hack into the database structure with CRM. This book makes it very clear (as do other CRM experts I know) that I shouldn’t, but I keep looking out for a situation that will persuade me to start playing. I’m not talking about massive changes, but small things, like additional indexes for example. This book is likely to persuade me not to dabble, as I want to do things its way. Whilst nothing jumped out at me as being a really good argument not to (although I might’ve missed it somewhere), I felt myself being guided down the proper way of programming CRM, and now think that if this book recommends a particular path, I’m probably going to follow it as closely as possible.

So now I’m wondering how long it’s going to be before I find myself involved in a CRM project, and wondering whether I’ll consider myself ‘learned’ or not. I still know there is a lot to learn with CRM, but I also know I have one of the best resources out there on my bookshelf.

A Tripp to Melbourne?

Rob Farley — Mon, 19 Oct 2009 06:53:07 GMT

I feel like it’s been a while since I’ve been to Melbourne (although I did pass through the airport there on my way back from Wagga). I don’t know when I’ll be there next, but I have felt tempted to try to get there this week.

Partly it’s because my mum is about to turn sixty, but also because my friends Kimberly Tripp and Paul Randal are in Australia this week, and speaking at the Melbourne SQL Server User Group tomorrow night.

I won’t be there, but if you’re going to be in Melbourne, then make sure you register and get yourself there. It’s bound to be quite packed, as these guys are the world experts in their areas – so be early and tell them hi from me!

A busy month – a new book, a new car, a new phone

Rob Farley — Wed, 30 Sep 2009 12:50:00 GMT

I don’t know why Septembers are always busy. This one feels like it’s been interesting, and I’m not sure life will be the same again.

But first, some of the biggest news – the book that I wrote a couple of chapters for is now available for purchase!

A challenge was put out a while back for SQL MVPs to write a book for charity. Paul Nielsen spearheaded it, and I’m pleased to say that there was a massive response. I wrote two chapters, and this week we have had the notice that the book can now be purchased from Manning Press. If you go to http://www.SQLServerMVPDeepDives.com you will be able to buy the Early Access Edition, which will get you updated electronic copies as the chapters become available (final layouts, images, etc still appearing). All the royalties for this book go to charity rather than the authors, so buy up! I promise to sign any copy put in front of me, but if you go to the PASS conference in November, you can probably get at least 40 or so of the other authors to sign it instead.

I’m planning to get a signed copy brought back from the US, and will auction it off to members of the Adelaide SQL Server User Group, giving the money to charity too.

It feels good to have the book finished!

September has also busy for a number of reasons. The company is growing nicely, celebrating a year this week, and keeping my time somewhat occupied. We achieved Gold Partner status with Microsoft at the end of August, and are ticking along well. On a more negative note, the winter has taken its toll with flu in the family, which is lousy – but we also bought a new car (finally got the people mover we’ve been promising ourselves). I replaced my old phone with an iPhone (part of me thinks that I’ll go back to Windows Mobile next time), and bought my wife one too (plus a DS for her birthday). I feel like we’re more gadgety than ever!

Not to mention TechEd Australia, which was a fun time. Grant Paisley surfing at Dreamworld was a sight to behold, and I hope there are photos somewhere! I gave a talk on SQL Azure, which gave me a number of headaches leading up to the conference, finding new things I wanted to mention on a daily basis! I also gave a talk on the danger of scalar functions in SQL Server, which I will be repeating in just over a week at Wagga, a couple of days after presenting at the Albury/Wodonga .Net User Group. An email arrived about half an hour after my scalar functions talk, saying that someone in the audience had just applied the principles I showed and made some vital queries run thirty times faster! Terrific news I think.

Bringing another laptop (an HP Mini) home from TechEd has also added to the number of gadgets in the house…

Tomorrow I hope to be able to write that I have been awarded MVP status for another year, which will be a tremendous honour. I keep wondering how much longer I’ll be able to remain in the company of such a fantastically skilled and helpful crowd. Every time I receive the award I’m both humbled and proud, and feel amazingly blessed.

More SQL Conferences coming up, including SQL Bits and SQL Down Under

Rob Farley — Thu, 27 Aug 2009 12:06:33 GMT

I know I won’t be there, as I’m a million miles away in Australia, but being from the UK myself, I always have an interest in the UK SQL community and in particular, events like SQL Bits.

This is the fifth SQL Bits conference, and they keep getting larger and larger. I’ve heard it’s now the largest SQL-focussed event in Europe. It’s going to be in South Wales (that’s OLD South Wales, not New South Wales), in November. I’m sure the area is lovely, good beaches ‘n all that… but considering it’s late November in Wales, I think you’ll be going for the SQL content, not the scenery.

Of course, if you are in New South Wales, then you ought to be thinking slightly earlier, in particular, the second weekend in October. The third SQL Code Camp is being held in Wagga, with many regular speakers (like myself) and quite a few new ones too.

These two events are clearly the significant SQL events in the last quarter of the year. I’m sure no-one cares about SQL PASS, after all. (I do wish I was going to this one, but I won’t be. I plan to go one year, but I was in the US that week last year, and I don’t plan to be away from home for two birthdays in a row. Maybe next year. It is the biggest SQL event in the world, with great speakers from everywhere, including many good friends of mine.)

No matter where you are in the world, there are SQL events that you should be going to. Professional development is really important for your career, and you shouldn’t neglect it. That being said, make sure you find me at TechEd Australia.

Still learning… foreign keys don’t need to reference a primary key

Rob Farley — Tue, 04 Aug 2009 23:09:15 GMT

…but you should still have a primary key on every table of course.

It’s just that I only recently discovered that you can have a foreign key that references something else, so long as it’s known to be unique through a unique index / constraint.

The scripts here demonstrate this in SQL Server 2005 and beyond.

create table testunique (id int identity(1,1) primary key, otherid int);
go
create unique index ixOther on testunique(otherid);
go
create table testFK (id int identity(1,1) primary key, someid int)
go
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)
go

And then if I try to drop the ixOther index, I get an error saying:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index 'testunique.ixOther'. It is being used for FOREIGN KEY constraint enforcement.

So I guess this is another reason not to blindly remove indexes that aren’t mentioned in sys.dm_db_index_usage_stats

SSRS: Removing the Navigation link using an Expression

Rob Farley — Tue, 04 Aug 2009 13:22:59 GMT

There are times when you want to have a Navigation property of a textbox (typically providing a link to a URL or other report), but you don’t always want the link to be there. Sometimes you just want it to be an ordinary textbox.

This particularly applies when you’re using a Matrix, and you don’t want the Subtotal rows to have the navigation links. Previously I’ve blogged about using InScope to control various properties. But the thing that I hadn’t noticed was how to make the link actually disappear. I could make it point at somewhere less useful (like the current report), but I didn’t want the cursor to change.

And then my friend (and fellow MVP) Jason Strate told me that if you make the Expression give the result of “Nothing”, then this does the trick.

So try something like: =iif(inscope("matrix1_SalesPersonID"), “http://someurl.com”, Nothing)

Thanks, Jason!

A review: "Learning SQL Server 2008 Reporting Services” by Jayaram Krishnaswamy

Rob Farley — Sat, 25 Jul 2009 12:19:02 GMT

A while back I got asked if I’d review this book, but as they could only get me a copy in PDF format, it’s taken a while to get to it. Whilst I like having electronic copies of books, I generally prefer to read them in paper form, and just have the electronic copy for reference.

But anyway – this book calls itself “A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008”. At 536 pages, I remember my first thoughts being that it seemed awfully long for the level that it’s aimed at. However, as I went through it, I found that this was largely down to the sheer number of screenshots. This is great, and particularly for an eBook. It means that someone trying to learn about a particular screen can easily click the “Next Page” button until they see that screen.

As for whether or not I’d recommend this book to someone learning Reporting Services – well, it depends on your style of learning.

If you want something deeper, that explains more about the choices you’re making, and the paradigms you should be considering, then maybe this book isn’t for you.

But if you want to jump into SSRS, looking at example after example, to get a hands-on feel for the product, then this book is great. It feels like labs when you’re reading it, and that’s going to suit a lot of people.

Counting consecutive instances with DENSE_RANK

Rob Farley — Fri, 24 Jul 2009 23:34:52 GMT

It’s hard to think of a practical use for DENSE_RANK(), but here’s one.

It’s one of the ranking functions that was introduced in SQL Server 2005, along with RANK(), NTILE(n) and the incredibly useful ROW_NUMBER(). But apart from producing reports, it’s quite hard to find genuine uses for some of them.

Yesterday I answered a question at StackOverflow (first time – I often answer questions at Experts Exchange and on the MSDN Forums, but never at StackOverflow before). It’s at http://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255

The problem is trying to find runs of consecutive days of activity. I solved it using ROW_NUMBER() and the integer value of the day, grouping by the offset between them. When you partition the ROW_NUMBER() by the users, you have a complete solution. It works nicely.

But if people can have multiple entries in a day, then ROW_NUMBER() doesn’t cut it – the offset would change whenever someone didn’t have exactly one record per day. Of course, there might be logic in place to make sure this never happens, and I hope there would be – but if there isn’t the problem just becomes one for DENSE_RANK().

You see, DENSE_RANK() won’t go up for ties. If you have two entries on the same day, they’ll be given the same rank. And then the next day will be as if there had only been one the previous day. If you have ten 107ths, the next DENSE_RANK() is 108 (RANK() would give the next value as 117).

So then my query of:

with numberedrows as
(
select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset;

…becomes:

with numberedrows as
(
select dense_rank() over (partition by UserID order by cast(CreationDate-0.5 as int)) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), datediff(day, min(CreationDate), max(CreationDate)) + 1 as NumConsecutiveDays, count(*) as NumEntries, UserID
from numberedrows
group by UserID, TheOffset;

Bear in mind that I’m having to truncate the CreationDate here. If the values were stored as an date with no time component, I’d be able to use CreationDate without modification, which lends itself much better to indexing strategies. For this scenario, I’d prefer to have an index on a computed column which was the truncated date.

So there you go – a practical use for DENSE_RANK().

Annoying SharePoint error about features and templates

Rob Farley — Tue, 21 Jul 2009 07:11:59 GMT

Following a successful installation of SharePoint (WSS) and making a new web application using a content database restored from another server, this annoying error appeared when on one of the sites.

Feature ‘75a0fea7-2d1e-451a-b445-16bc346d78e’ for list template ‘1’ is not installed in this farm. The operation could not be completed.

I say “annoying” because it seemed really hard to fix – hence this blog post.

By Binging for that GUID, I could see that it was related to the BugList template. That made sense, because the site in question is focussed on that. But I couldn’t see how to get the BugList template working. Being a feature (from the error message), I figured it would help to run SharePoint.exe (the setup program) again, but the only options in there were Repair and Remove, neither of which was looking like helping. Running the “SharePoint Products and Technologies Configuration Wizard” didn’t help either – there was nothing in there about features or templates.

A colleague pointed me to an application template download site, which helped, giving me a bunch of .stp and .wsp files, one of which was BugDatabase.wsp. Not BugList, not Bug Tracking, not Bug Tracker, but Bug Database. I should’ve taken a hint from here, but anyway.

A bit more Binging (and looking in the readme.txt that came with the download), and I found a page that told me how to install a .wsp file – I needed to use stsadm. But first I needed to install ApplicationTemplateCore.wsp. I wasn’t entirely convinced it was going to be the right solution, but I gave it a try.

“\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN\stsadm.exe” –o addsolution –filename e:\AllTemplates\ApplicationTemplateCore.wsp

It didn’t work at first, but it turned out that I was copying the command line from a website, and it can’t’ve been recognising one of the spaces. I typed it in myself, and it worked first time.

There were more instructions on deploying the solution, but it turned out those instructions weren’t quite so useful, because when I went into the Central Administration site, in Operations, Solution Manager, I found that the deployment hadn’t been successful, despite stsadm telling me that it had been. So the next time I had to do this, I just added the solution with stsadm and then went straight to the Solution Manager, because I could deploy it from there.

In the Solution Manager, you click on the applicationtemplatecore.wsp entry and hit the “Deploy Solution” button. Hopefully it will work for you, as it did for me.

Terrific! Half way there.

Now I could do the same for BugDatabase.wsp.

“\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN\stsadm.exe” –o addsolution –filename e:\AllTemplates\BugDatabase.wsp

..and go into Solution Manager to deploy it.

Now my site was up. But when I went into the Issues List, that was broken with a similar message, just a different GUID and template number.

Feature ‘60d1e34f-0eb3-4e56-9049-85daabfec68c’ for list template ‘1010’ is not installed in this farm. The operation could not be completed.

I could see this was a feature called pwsissues (thanks again Bing), but I couldn’t see a .wsp file that suggested anything. I hunted around for clues, even came across a discussion at West Michigan SharePoint User Group about it. It didn’t really help much though, I was still hunting around trying to figure out what I had to do. I felt it should be similar to my previous problem, so in desperation I started adding .wsp files indiscriminately.

And it worked! I should’ve tried this earlier, remembering that BugDatabase.wsp wasn’t called BugTracking.wsp. To get the Issues Tracking template in, I needed to add (and deploy) the solution called ProjectTrackingWorkspace.wsp.

Once I had done that, my errors disappeared, and I breathed a big sigh of relief.

I quite like SharePoint – it’s a very useful tool for many reasons. I guess I just haven’t had quite enough SharePoint _admin_ experience. But now I have a blog post to refer to if I come across this error again.

A really good book on BI

Rob Farley — Mon, 20 Jul 2009 12:45:22 GMT

Business Intelligence is so much more than the technologies involved. Doing BI well is about delivering the right solution for the client, and being able to plan accordingly, about finding a match between the technologies and the business needs, and about being able to come up with a good design that incorporates not only the data warehouse, but also the OLAP database and client interfaces.

When I first picked up Smart Business Intelligence Solutions with Microsoft SQL Server 2008, I wasn’t sure what to expect. I didn’t know whether it would be design heavy, or technology heavy. I suspected it would be the latter, but without going deeply enough into the technologies.

But I was pleasantly surprised. It seems to be heavyweight enough in both design and technology. There are useful sections on every technical aspect of Business Intelligence, including Data Mining, Beginner and Advanced MDX, and more. And yet there are useful sections about BI principles – the kind of things that you come across in books by Kimball.

I feel like there are an increasing number of really good books about SQL Server, and this is definitely one.

Hey Microsoft – let me restrict my network connections better!

Rob Farley — Sat, 18 Jul 2009 05:59:27 GMT

I’m sure you know the feeling… you jump online by tethering to your mobile because you want to check a particular website, and all of a sudden you find your connection is being flooded by a Windows Update, or Live Mesh download, or a Sync Framework thing. Perhaps even Outlook is downloading your Junk Mail folder for you.

But all you wanted was to check what the weather was – but it’s just cost you a fortune in download fees.

Situation is that running applications will check for an internet connection, and then take advantage of it – with no consideration about whether it’s going to cost you (per kilobyte!) to use that connection.

I would love to see a way of configuring my network connections (like how it asks me whether I’m at Home, Public or Work) into a profile, along the lines of:

Anything connect – all my apps can use this
Sometimes connect – apps need to ask me to use the connection before they do
Never connect – I would need to explicitly allow an application to use this connection

When I connect using an account that’s limited by size, I don’t want Silverlight to download a ton of images, or Windows Update to pull down a Service Pack. But I would be happy for a particular instance of a browser to connect out.

But when I’m at home, I’m happy for anything to be pulled down.

I’d probably end up using the “Sometimes connect” option a lot, so that my connection is more ‘available’, even when one of my applications decides to pull a ton of information down – but right now, connectivity is either ON or OFF, and I can’t easily stop applications from using it when it’s ON.

Please, Microsoft… let me choose this. It’ll help me feel okay about connecting while my applications are open (and not have to quit Skype/Msgr/LiveMesh before I connect).

Four speaking engagements coming up

Rob Farley — Thu, 16 Jul 2009 10:55:20 GMT

I’m just going to list them…

This Saturday (July 18th), at Code Camp SA. I’m going to be talking about functions in SQL, particularly those that involve BEGIN and END.

At the end of the month, at the ACS Branch Conference. I’m going to be part of a panel discussing Open Source v Closed Source.

In August, I’m going to be speaking at SharePoint Saturday (Adelaide), about the integration of Reporting Services and SharePoint.

In September, I’m going to be a presenting at TechEd Australia, about SQL Azure.

Be nice if there was more of an overlap in topics…

DeepZoom fixoutlook – and a tip on hosting SilverLight

Rob Farley — Thu, 16 Jul 2009 10:31:02 GMT

I haven’t done much SilverLight development – I’ve dabbled at best. And I don’t really count using DeepZoom Composer as proper SilverLight, but it makes a SilverLight application.

So I’ve got one at http://www.lobsterpot.com.au/fixoutlook

The background is that there’s a group of people who feel very strongly that Outlook should use proper standards for rendering emails. I understand that Microsoft have made a conscious decision to use Word for rendering emails, but I’m certainly part of the crowd who agree that Microsoft may have got this one wrong. They made a large image, and I thought this image would made a great candidate for DeepZoom. I dropped Dave a line, and he gave me the go-ahead.

So have a look around my DeepZoom application, and in particular, zoom in at the top of the “L'’ in the word outlook, to find how much you can zoom in. Look for the white lines, and you’ll see some more zooming opportunity.

And a big thanks to John O’Brien, for reminding me that when you host a SilverLight application, you need make sure the mime types are set appropriately.

The new brand is Microsoft SQL Azure

Rob Farley — Thu, 09 Jul 2009 11:57:12 GMT

It used to be SQL Server Data Services. Then SQL Data Services. Then we started hearing about SQL Services, and now (I hope finally), we have Microsoft SQL Azure instead of SQL Services, and the Microsoft SQL Azure Database instead of SQL Data Services.

http://blogs.technet.com/dataplatforminsider/archive/2009/07/08/microsoft-sql-services-is-now-microsoft-sql-azure.aspx

I think this is a sensible move. For a while now, whenever refer to SQL Services, I have to make it clear that it’s not the Windows Services that run SQL on your server, but that it’s the “in the cloud” offering that Microsoft are bringing out. Now, I can talk about SQL Azure, and it’s very clear what it means. And SQL Azure Database is clear too, as opposed to whatever will come, like (and I’m predicting here, haven’t heard anything) SQL Azure Analytics or SQL Azure Reporting.

This is definitely a positive move, and should hopefully help people realise that this is not the same as SQL Server, help them approach their SQL Azure platform slightly differently to their On-Premise equivalent.

For example… At the moment, the SQL Azure Database platform is limited in size. I hope it will soon be unlimited, but for the time being there is a limit which will affect development decisions. If your database suddenly needs to store a lot more data than you expected, you may need to hurriedly work out how you’re going to handle this. But I think having a name which reflects the Azure nature will prompt developers into making this decision earlier.

I’m looking forward to getting my hands on SQL Azure, and seeing how my job will change because of it.

Excel dates counted differently and a reliable way of working out the day of the week

Rob Farley — Sun, 05 Jul 2009 11:11:28 GMT

Following my recent post about 40,000 days, I got a couple of emails telling me that Excel disagrees about when the 40,000th day is. And this is true – Excel counts Day 40000 as July 6th 2009, not July 7th.

Unfortunately for Excel users, they’re wrong. And it’s down to the fact that Excel thinks that 1900 was a leap-year, when we all know it wasn’t. You see, leap-years only fall on the century year if the year is divisible by 400. So 2000 was a leap-year, but 1900 wasn’t, and 2100 won’t be either.

What’s more, this has been a problem for ages, and it was a conscious decision to introduce the bug. Essentially, Lotus 1-2-3 thought 1900 was a leap-year, and to be consistent with the market leader of the day, Microsoft treated 1900 as a leap-year too. And nowadays, we have a situation where backwards compatibility is more important. Today, if you have an application which knows that day 40000 is July 7th, then you’d better not treat your date that same way in Excel (or vice-versa).

The thing that I find really amazing with this is that Excel tells me that Feb 29, 1900 was a Wednesday. But it didn’t exist – so what happened that week? Actually (and you can check other calendars for this, including Windows’ one), Feb 28th was a Wednesday, and Excel gets the day of the week wrong for the first 59 days of its calendar.

I know you don’t care, but perhaps you should – in case you ever write an application that needs to know what day of the week it is.

Working out the day of the week is really trivial. For instance, in SQL Server, you can generally ask for the DATEPART(dw,…) of the date in question, and get a number back, telling you what day of the week it is. It’ll tell you 1 for the 1st day of the week, 2 for the 2nd, and so on.

Which is great, until you find that someone in your organisation says that Sunday is the first of the week, but someone else insists that it’s Monday. In the movie industry, I think Thursday is the first day of the week. So then, when is the 5th day? In SQL we have @@DATEFIRST, which helps a lot, but a method I like to use is to count the number of days since a known Sunday (or whatever), and take the “mod 7”. If that’s zero, I’m an exact number of weeks since that known Sunday. It works nicely, and it’s simple enough for everyone to understand (and it works regardless of location or other changeable settings).

But if you had picked your “known Sunday” in early 1900 using Excel, you’d’ve got it wrong, and your data might not work if you push your system out to SQL later (so pick something later – like 1901). I recently dealt with a date dimension that someone had put together in Excel and imported into SQL – if this data had gone back to 1900, then there would’ve certainly been errors in it (for a start, the import wouldn’t’ve worked because SQL would’ve complained that Feb 29, 1900 wasn’t a valid date).

My preference with date dimensions is to use a lot of computed columns, and only ever populate a single field. It works nicely, and it’s almost no effort to extend the table to include extra dates when required.

Big events every month this quarter

Rob Farley — Wed, 01 Jul 2009 02:23:47 GMT

A new Financial Year in Australia, and a bunch of technical events coming up.

Of course there’s the usual monthly user groups, but there’s more – particularly if you’re in Adelaide.

July sees CodeCampSA in Adelaide on the weekend of July 18/19. I’ve put my name into the hat for speakers, and will try to be there for a chunk of Saturday (Sundays are too busy for me). I’m sure at least one of my sons will want to come along as well, which will be fun. Big thanks to David Gardiner for putting the website together.

August sees SharePoint Saturday come to Sydney (8th) and Adelaide (15th). You may not agree with Aaron about what SharePoint is, but if you’re into SharePoint, I’m sure you’ll get a lot out of these events.

September brings Australian geeks to the Gold Coast again for the Microsoft’s annual TechEd Australia, this year with the added incentive of an HP Mini for attendees (conditions apply of course). It’s the 2140, which is a discontinued line, but that doesn’t make it any less attractive a machine. I’m sure this will help persuade people to get themselves over to Queensland.

And in case you hadn’t realised, I’ve recently discovered how easy it is to put maps into blogs using Windows Live Writer… just so that you can all see the beach, and understand how poorly attended the sessions would be if they ran TechEd Australia in the summer.

40,000 days since the beginning of ‘time’

Rob Farley — Wed, 01 Jul 2009 00:12:33 GMT

Next Tuesday feels like a significant day. Not only is it 7/7, and the fourth anniversary of the London bombings (in 2005), but it’s 40,000 days since the beginning of time. Well, not proper time, but since the start of the 1900s.

SELECT DATEDIFF(day,'19000101','20090707')+1 AS DaysOf1900s

So if Day 1 is Jan 1, 1900 (as many systems tend to use – SQL actually considers it Day 0), Day 40000 is 7/7/2009.

I actually rate the start of the twentieth century as a very significant event in the human race. In 1900 the world was changing faster than it had ever done before, and no-one can deny the significance of the twentieth century in history. I’m sure change will continue to become faster, but I found it interesting recently when I noticed that we were approaching this ‘milestone’ of sorts.