Converting minutes from midnight

Wednesday, 7 May 2008

Jane asked me how to convert "minutes from midnight" into hours and minutes in Excel.

Turns out the TIME() function can do this for you.

Say you have 585 minutes. Use it like this:

=TIME(0, 585, 0)

Then format the cell for time, eg. "h:mm", and you get

"9:45 AM"

Just what the doctor ordered!

Before you ask, no I'm not an Excel expert, but I am pretty good at knowing where to find the answer most of the time.

Hotmail rejecting forwarded email

Tuesday, 6 May 2008

I've been using ZoneEdit to manage the DNS for our domain gardiner.net.au with reasonable success. About the only problem I've had is every few months, I'd discover that email sent to my wife's Hotmail account (via her @gardiner.net.au address) is rejected with a message like the following:

<[email protected]> (expanded from [email protected]): 
host mx4.hotmail.com[65.54.244.232] said:
550 SC-001 Mail rejected by Windows Live Hotmail for policy reasons.
Reasons for rejection may be related to content with spam-like characteristics or IP/domain reputation problems. If you are not an email/network admin please contact your E-mail/Internet Service Provider for help. Email/network admins, please visit   http://postmaster.live.com for email delivery information and support (in reply to MAIL FROM command)

To their credit, the Live Hotmail guys are pretty quick off the mark to resolve this once you submit a request via their support page.

I think the problem is that because ZoneEdit hosts so many domains, some of those turn out to be used (or hijacked) by spammers, and so inevitably their IP ranges end up on spam black lists. ZoneEdit don't have any SPF records for their mail servers either which probably doesn't help.

So tonight I bit the bullet, and signed up with Google Apps to manage the mail for our domain. I'm hoping that pointing my MX records to Google will mean that things all look legitimate when Hotmail goes to verify that the domains match up with the SPF record.

Signing up was pretty straight forward. After verifying that I control the DNS records, I added accounts for all the people who have @gardiner.net.au email addresses, then logged in and configured their email to forward to their preferred address. There's even a 'test' email address so you can try things out before you update all the MX record information.

We'll see if this turns out to be a good idea or not.

Book prices

Tuesday, 6 May 2008

I recently ordered MCPD Self-Paced Training Kit (Exam 70-547): Designing and Developing Web-Based Applications Using the Microsoft .NET Framework from Amazon and it cost me about $AU 60 (including postage).

MS Press have just launched their Australian web site, and the same book is listed as $AU 120. Dave Glover mentions that user group members can get a 50% discount, but only for this month. So for this month at least they are comparable.

It isn't just MS Press though. I've seen other Australian booksellers listing this same title at equally uncompetitive prices. About the only reason you'd buy local is if you didn't want to wait a couple of weeks for Amazon's standard delivery to arrive.

Certainly, the Australian dollar is very high at the moment, so that helps with the exchange rate, but I still don't get why there's such a huge price difference.

Ready, Steady, Code

Monday, 5 May 2008

Once or twice while I was on leave recently, I happened to have the TV on during the daytime. One of the programs that was on was Ready, Steady, Cook (originally a BBC show apparently).

The basic format of the show is as follows:

So what I was thinking was whether you could adapt this format to a user group setting?

Have two teams (ideally with two data projectors so the audience can see what they're doing), and give each of them 3-4 random technologies and they have to come up with an application that uses all of them in a fixed time period.

At the conclusion the audience can vote on which they think is the best.

You might need a little more time, and the audience could get involved shouting out suggestions if they get stuck.

Ordering data using dynamic SQL versus temporary tables

Thursday, 1 May 2008

Here's a scenario that's not that uncommon:

You have a query and the user wants to be able to choose at run-time which column to sort/group by.

The problem is you can't use a variable to indicate the column name in the ORDER BY or GROUP BY clauses.

If the query itself is very simple, then you could just repeat the query with specified ordering using IF. eg.

IF (@order = 'Column1')
SELECT * from Table1 ORDER BY Column1
ELSE IF (@order = 'Column2')
SELECT * from Table1 ORDER BY Column2

If the query is non-trivial, you probably wouldn't want to use this however, as maintenance becomes problematic.

There are two obvious solutions to this:

Dynamic SQL

Assemble the query into a varchar variable and then execute it using sp_executesql.

DECLARE @sql varchar(4000)

SET @sql = 'SELECT * FROM Table1'

IF (@order = 'Column1')

	SET @sql = @sql + ' ORDER BY Column1'

ELSE IF (@order = 'Column2')

	SET @sql = @sql + ' ORDER BY Column2'

sp_executesql @sql

Temporary Tables

Split the query into two parts. First of all, retrieve the data into a temporary table (this is one of the few times you can't use a table variable). Next, use the original IF .. ELSE IF method to return the data from the temporary table.

SELECT * INTO #Stuff FROM Table1

IF (@order = 'Column1')
	SELECT * FROM #Stuff ORDER BY Column1
ELSE IF (@order = 'Column2')
	SELECT * FROM #Stuff ORDER BY Column2

So which is better? Great question, and I'm not really sure of the answer. I suspect it could well be "it depends".