Making date and time sense from the SQL Server Agent job history table

Sometimes you’ve got to wonder why database developers do things the way the do. Take for example the sysjobhistory table in the SQL Server msdb database. Its got two awful columns namely run_date and run_time. Why are they awful you ask? Well because they are integer representations of the date and time. Just look at the following examples to see the stupidity.

run_date run_time Actual
20091123 230000 2009-11-23 23:00:00
20091123 230001 2009-11-23 23:00:01
20091123 234500 2009-11-23 23:45:00
20091124 0 2009-11-24 00:00:00
20091124 0 2009-11-24 00:00:01
20091124 1000 2009-11-24 00:10:00
20091124 3000 2009-11-24 00:30:00
20091124 10000 2009-11-24 01:00:00
20091124 10636 2009-11-24 01:06:36

Now if you’re wanting to do something meaningful with them you’ll want to represent them as a DATETIME data type most likely. After searching for a quick and easy way I decided to hack a quick bit of TSQL up and put it up here so hopefully others might benefit.

SELECT    CONVERT(DATETIME, 
            STUFF(STUFF(CONVERT(VARCHAR, [run_date]), 7, 0, '-'), 5, 0, '-') + 
            ' ' + 
            STUFF(STUFF(RIGHT('00000' + CONVERT(VARCHAR, [run_time]), 6), 5, 0, ':'), 3, 0, ':'), 120)
FROM    [msdb]..[sysjobhistory]
 

Let me know in the comments if you found it useful or if you have a more elegant way. If you use it a lot, make a user-defined function taking in the two values. I leave that in your capable hands.

Silverlight 4 beta and my disappointment with the WebBrowser control

Silverlight 4 logo by Tim Heuer So as most of you probably know by now, Silverlight 4 has been announced and a beta is already released, you can head off over here to find out what’s new and to download the bits you need like Visual Studio 2010 Beta 2, etc. One big warning though, if you have the Silverlight 3 SDK installed on your machine, uninstall it before installing Visual Studio 2010 as it might barf the Visual Studio 2010 installation.

So one of the features I’ve been eagerly awaiting in Silverlight 4 is the new WebBrowser control. Yeah as strange as it may seem I want to embed some web content in a Silverlight control. Well not a control as such but more a Silverlight application. And I’ve managed to do it since Silverlight 2 by creating iframe elements in the underlying HTML DOM and absolutely positioning them over my Silverlight control, all from within the Silverlight application. This has however one major limitation – you can’t put Silverlight content on top of the HTML iframe unless its in a seperate object element which doesn’t exactly suit my needs.

Silverlight 4 WebBrowser doesn't play niceAnd it turns out that the Silverlight 4 beta WebBrowser control suffers the same horrible fate, as you can see in a screenshot of a quick test application on the left. Except it has another awkwardness to it – it has to be run out of the browser as well. Looks like I’ll be sticking to my iframe approach for in the browser and consider using the WebBrowser control for out of the browser only.

Now don’t get me wrong, I do like the new control, but I would like it to be even better. If anyone on the Silverlight team reads this, please make the WebBrowser control a proper Silverlight control that can have pure Silverlight controls over it.

Silverlight 3 beta is available

Most of you knew it was coming and I’m really excited that its out now so I can talk about it. Silverlight 3 really is very cool and adds amazing performance improvements and additional features that people have been wanting.

My personal favourites are:

  • Hardware graphics acceleration – Our application just feels way more zippy than before.
  • Tighter XAP file compression – Everyone benefits from better ZIP compression ratios.
  • External styles – Now you can reference an external style. Great for skinning applications.
  • Out of browser/offline mode – Finally a competitor for the Adobe AIR platform.

If you are currently developing using Silverlight 2 I recommend that you install the new Silverlight 3 beta bits in a virtual machine. You can get them all from here.

ASP.NET MVC 1.0 Ships

Microsoft .NET logo After months of community beta testing the first release build of the ASP.NET MVC (Model View Controller) framework has been released. If you are a web developer and don’t know what I’m talking about, head on over to the ASP.NET MVC site where you can find a wealth of tutorials, videos and oodles of samples to get you started.

What excites me about the ASP.NET MVC framework is that web developers will hopefully be moving away from the legacy forms based approach while sticking with the familiarity of the ASP.NET framework. Scott Hanselman pointed out at the talk he gave SA Developer .NET in December 2008 how ASP.NET MVC and ASP.NET WebForms merely build on the rich plumbing framework that is ASP.NET. Most people tend to get confused about that.

You can download the ASP.NET MVC installer from here.

Microsoft Silverlight 2 released

After many months of beta testing Microsoft Silverlight 2 it has been officially announced that it will release today, the 14th of October 2008.

A release build was expected to emerge sometime in November so it seems the team is very confident with the RC0 developer-only build that emerged recently. Unluckily for my team we just released our new product on Friday based on Silverlight 2 beta 2 under the Go-Live license thinking we had at least a month before Silverlight 2 released to fix up compatibility issues. Looks like this week is going to be a busy one for us.

Here is a snippet from the official press release:

Silverlight 2 will be available for download on Tuesday, Oct. 14, at http://www.microsoft.com/silverlight. Customers already using a previous version of Silverlight will be automatically upgraded to Silverlight 2.

What is awesome though is that Microsoft will soon release an extra set of Open Source controls known as the Silverlight Control Pack including a DockPanel, ViewBox, TreeView, Accordion and AutoComplete control.

A practical use for filtered indexes

Have you ever wanted to create a table in SQL Server with a nullable column that should be unique as well? I have often needed this and resorted to defining a new table that links to the master table. But SQL Server 2008 has the solution to this, see my example below.

CREATE TABLE [dbo].[Person] (
      [Id]              INTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      [FirstName]       NVARCHAR(50) NOT NULL,
      [LastName]        NVARCHAR(50) NOT NULL,
      [EmailAddress]    VARCHAR(256) NULL
);

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_0] UNIQUE NONCLUSTERED ([LastName], [FirstName]);

Next consider adding a unique constraint or unique index on the EmailAddress column to make sure its unique.

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_1] UNIQUE NONCLUSTERED ([EmailAddress]);

So far so good, we can now add some test data.

INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Smith’, ‘bob@smith.com’);
INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Jones’, ‘bob@jones.com’);
INSERT INTO [dbo].[Person] VALUES (N’Mary’, N’Smith’, NULL);

Next try insert another person with no email address.

INSERT INTO [dbo].[Person] VALUES (N’Frank’, N’Smith’, NULL);

Whoops, looks like our unique constraint is not going to let us have more than one NULL in the table. Which is correct. So how can we fix this, well this is where the new SQL Server 2008 filtered indexes come in. We simply create an index with a WHERE clause as follows:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Person_EmailAddress]
      ON [dbo].[Person] ([EmailAddress])
      WHERE [EmailAddress] IS NOT NULL;

And then we can insert our new record without having the problem as we did with the unique constraint.

Unfortunately there is a limitation to how this can be used. A unique index cannot be used in a foreign key constraint. I just wish the SQL Server team would add the WHERE clause, and while they are at it the INCLUDE clause as well, to unique constraints.

Thanks to Gail for the suggestion.

SQL Server Spatial Tools now available on CodePlex

Isaac Kunen has posted details about the new SQL Server Spatial Tools project on CodePlex containing functions that can be used when working with the new SQL Server 2008 spatial data types.

The core idea is that instead of pushing up more samples showing how to extend our spatial functionality in text, we can put them in a more usable form.  Beyond just showing off some samples, though, we’d like to assemble useful code into a toolkit that provides some real functionality over what we bake into the server.

For a list of the goodies that the package contains take a look here and go and download the source code.

Time to monetize your gaming creativity

XNA Creators Club Online We all knew it was coming and finally its confirmed and coming to an Xbox 360 console near you later this year. Using the free XNA Game Studio 2.0 coupled with Visual C# Express Edition from Microsoft you can now not only design and build your own games to run on Windows and the Xbox 360 gaming console, but now you can share them with the community through the Xbox LIVE network of over 12 million members. I’m assuming the new feature will be delivered with the New Xbox Experience later this year called Xbox LIVE Community Games.

As a Premium member in the XNA Creators Club, you’ll be able to submit any complete Xbox 360 game you’ve created in XNA Game Studio to the Creators Club community at http://creators.xna.com, for peer review. Other Premium Creators will check to make sure your game is safe to play. If it is, you’ll set a price point – between 200 and 800 Points – that people will pay to download your game.

Once the game is reviewed and the price point set, you’re done. The game is listed on Xbox LIVE Marketplace, and you’ll get a check every quarter, for up to 70% of the game’s total revenue in your own currency. Depending on your game’s success, you may even have your game advertised on Xbox 360 and other Microsoft online properties.

Just imagine – your game in the hands of millions of Xbox 360 gamers around the world: that’s the power of Community Games.

Make games, Make money But wait, that isn’t even the best part. You can also put a price tag of between 200 and 800 Microsoft Points on your game and you’ll get a quarterly payment from Microsoft as users buy your game. Well you’ll actually get 40% to 70% of the revenue depending on how its marketed but normally it will be a 70% revenue share. Wicked stuff isn’t it? I think so.

The only big drawback right now is that its only available to XNA Creators Club Premium members in the U.S., Canada, U.K., France, Italy and Spain for now. They plan to add more countries to the list later in the year.

Head on over to the XNA Creators Club site and sign up, download the tools and while you are at it, enter the XNA Dream-Build-Play Challenge 2008 and win. Thanks Microsoft, this is an awesome idea for the aspiring game developer and a great way to grow the game developer community.

Managed type converters and Blend resolved

I previously posted about Blend 2.5 June Preview not supporting managed type converters and I’m pleased to report that the team has resolved it according to the bug report that I filed.

Hopefully we’ll see a new build of Blend 2.5 very soon as I’m quite tired of not being able to size DataGrid columns properly. In the meantime I’ve resorted to using the MinWidth property, but it isn’t quite the same.

Develop a game and win money

image

The 2008 Microsoft XNA Dream-Build-Play Challenge has begun and its looking like its going to be a really cool competition this year. The best part is that is a worldwide competition so that means even us South Africans can win.

There is a total of $75 000 up for grabs but everyone is a winner as everyone that enters gets a free 12-month XNA Creators Club trial membership.

The goal is to develop a groundbreaking game for the Xbox 360 platform using the free development tools Microsoft Visual C# 2005 Express Edition and Microsoft XNA Game Studio 2.0. To get started I recommend reading up more information on creating games over here. Good luck to all!