Seven steps to heaven

The Windows 7 Release Candidate build has been humming away happily on a test PC here for the last month, and I'm impressed up to now. So much so, in fact, that I'm very tempted to install it on my main working machine. Like I said before, it's stable, lightning fast, and has a host of useful improvements and additions.

The hands-down coolest feature I've come across this week, though, is the Problem Steps Recorder. This little applet is the answer to the prayers of helpdesks, independent developers (i.e. your humble servant) and anyone who's had to talk their parents through troubleshooting a faulty modem over the phone. What it does is record every action the user carries out in an application, allows him to comment on the problem and highlight problem areas on screen, then produces a simple but thorough step-by-step report including screenshots, full text and technical background, plus a slide-show of the same, in HTML format, zipped and ready to send. It works like a charm.

All I need to do now is persuade my customers to upgrade to Win7 pronto...

So long as we're being technical

...I may as well humbly draw your attention to some of the long-form technical articles on the main 360Data site. These are written and updated fairly sporadically; there are a thousand things I'd like to write about but finding the time to do it regularly is a problem...not to mention that all the checking that has to take place before one presents suppositions and assumptions as fact.

To @@ERROR is human, too

I started writing a short blog post about a particularly egregious example of bad transaction handling I encountered the other day, and the thing just ran away with me. First it became a long post, then a couple of examples were added and before I knew where I was I'd gone 6 pages further and it had become a full-blown article to explain the very basics of how error trapping, error handling and transaction handling works in SQL Server (PDF, 252KB).

To err is human

It's surprising how often database developers - or, at least, developers who are forced to develop a database - get really basic stuff wrong. Like choosing the correct data types, for instance.

I was asked to investigate a problem in an application database recently which, among other things, stored customer invoice details for the company. I was surprised to see that all the invoice line amounts, the VAT amounts, the discounts and so on were stored as float type data. I racked my brain to see if I could think of a good reason, but I couldn't. An invoice, and all the information on it, is a representation of real-world money, so you're never going to need anything more fine-grained than precision to two decimal places.

I can imagine, for instance, that you might have an Inventory table in your database, and you might record in it that each Yellow Widget weighs 0.023545 grams, or that the current sale price for each one is $0.1367, but by the time you're invoicing someone for 1256 of them, you're charging them $171.70 and not $171.6952.

We all make mistakes, though. Even in the future - check out this screen shot from The Terminator for another example of inappropriate data typing...

SQL Server 2008 R2

Official notice of the next SQL Server release begins to drip through. The next version will be called SQL Server 2008 R(elease)2 and encompasses a host of interesting new features.

Job dates made easy

Our good friends at Microsoft warn us all the time to stay away from the SQL system tables, but do we listen? No. Sometimes you just need information you can't get anywhere else, like (for instance) the date and time a SQL Agent job step runs (from msdb..sysjobhistory) or the time and date that a job is next scheduled (from msdb..sysjobschedules).

All well and good, but for their own undoubtedly sound reasons the architects at Microsoft decided once upon a time that these date and time values would be stored as integers in separate int columns, instead of in one datetime column. Even in SQL Server 2008, where separate date and time datatypes are available, the columns are defined as integers.

This makes comparisons and checks on these tables trickier than it otherwise should be. You can't just simply query on all job history from the past seven days, for example...unless you've got a function like this one:


create function [dbo].[fnGetJobDateAsDate]

(@p_JobDate int,
@p_JobTime int)

returns datetime

as
begin

-- Code © 2009 360Data

declare @v_DateStr nvarchar(48)
declare @v_Year int
declare @v_Month int
declare @v_Day int
declare @v_Hour int
declare @v_Min int
declare @v_Sec int
declare @v_JobDate datetime

select @v_Year = (@p_JobDate / 10000)
select @v_Month = (@p_JobDate - (@v_Year * 10000)) / 100
select @v_Day = (@p_JobDate - (@v_Year * 10000) - (@v_Month * 100))

select @v_Hour = (@p_Jobtime / 10000)
select @v_Min = (@p_Jobtime - (@v_Hour * 10000)) / 100
select @v_Sec = (@p_Jobtime - (@v_Hour * 10000) - (@v_Min * 100))

select @v_DateStr = convert(nvarchar(4), @v_Year) + N'-' +
convert(nvarchar(2), @v_Month) + N'-' +
convert(nvarchar(4), @v_Day) + N' ' +
replace(convert(nvarchar(2), @v_Hour) + N':' +
convert(nvarchar(2), @v_Min) + N':' +
convert(nvarchar(2), @v_Sec), ' ', '0')

select @v_JobDate = convert(datetime, @v_DateStr)

return @v_JobDate

end

The function requires date and a time parameter integer input in YYYYMMDD / HHMMSS format, just like in the msdb system tables, and returns a regular datetime value. Try it:

SELECT top 50 step_name, run_date, run_time, [YourDatabaseNameHere].[dbo].[fnGetJobDateAsDate] (run_date, run_time)
from msdb..sysjobhistory
order by run_date desc, run_time desc;

Result: happiness all around.