T-SQL Parameterized SELECT TOP @N

If you wanted to select the TOP N rows from a table based on a stored procedure parameter you would likely have tried:

SELECT TOP @N * FROM MyTable

Having come across the error:

Incorrect syntax near '@N'

You would next have hit google and, like me, found a lot of FUD surrounding performing what you would think would be a relatively simple operation.  You would have found talk about dynamic SQL, and no doubt all kinds of other hacks to get around it.  However, from SQL 2005, you can do this:

SELECT TOP (@N) * FROM MyTable

It's as simple as that.  Another good example where the highest ranked result isn't necessarily the right one.

Office 2007 – has its uses?

I've come across a lot of derision for Office 2007. Why have Microsoft completely changed the menu; what do the XML formats offer; isn't this just showing that Office has nowhere useful to go and you may as well use Open Office?

I've been using Office 2007 since the end of 2007 as our IT dept very quickly moved to Office 2007 (even though the rest of the business is using 2003 still, due to corporate requirements). It took a good while before I got used to the menu ribbons, and each of my team spent time staring at their screen wondering how to do what with 2003 was a trivial task. (Editing footer elements in a PowerPoint master page, for example.)

Over two years on since I began using it, I recently went back to Office 2003, and you know what – I couldn't find anything. Changing page orientation, which is two clicks with 2007 is a nightmare to get to with 2003.

Those suggesting using OpenOffice because it looks like Office 2003 are really missing the point. Whether you can immediately use it or not, Office 2007 has made usability improvements. It's just a matter of getting used to them. In a way Microsoft did take a big risk in making the change, but suggesting users use OpenOffice just because it's similar to Office 2003 really isn't the solution.

And another thing – Word 2007 supports common blogging platforms – including Blogger used for this post. Nice touch.

Twitter's Impending Doom: Apps and Games

Do you have that one annoying twitizen in your following list who talks jibberish all the time? Do you wish you could politely filter some/all of their tweets without blocking them entirely? Well, you can't. But don't worry, things are going to get a whole lot worse.

Twitter, by its very nature, treats all tweets equally. That's great, under the assumption all tweets are created equally. Unfortunately, apps like Twibbon, which trade a small service (adding a tiny graphic onto your profile pic) in exchange for an automated tweet from your account, change that, and they are only the tip of the iceberg.

Soon I predict a whole ecosystem of Twitter apps and games vying for access to your precious account login, and it will result in one thing: massive increase in twitter noise. (And you thought just having tweets your friends write is bad enough.) App invitations, quiz results, and spam, will all become part of your daily twitter traffic.

FaceBook apps and games, whether you love them or hate them, have two redeeming features: (1) you can block invitations for them, and (2) they are prohibited by FaceBook from forcing you to spam your friends (by, for instance, only allowing you access to the results of a quiz by spamming 10 of your friends).

Pretty soon Twitter will have to start factoring in recipient-based filtering of tweets depending on the origin app, and I don't believe Twitter will be able to move fast enough, with such enormous tweet volumes, to stem the tide. Without that, Twitter will quickly reach lowest-common-denominator proportions, and users will simply move on.

What happens if a statement fails in a SQL Server stored procedure?

Answer: it carries on regardless! In other words: if you want a stored proc to specifically stop upon error, test for it and return.

Take this example:

CREATE TABLE [dbo].[PaulTest](
[Number] [int] NOT NULL
) ON [PRIMARY]

CREATE PROCEDURE uspPaulTest AS
BEGIN
INSERT INTO PaulTest (Number) VALUES (NULL) -- fails as NULL is invalid
INSERT INTO PaulTest (Number) VALUES(1) -- still gets processed as the query doesn't return
END

-- Let's test it:

SELECT * FROM PaulTest

EXEC uspPaulTest

SELECT * FROM PaulTest

-- Output:

Number
-----------
(0 row(s) affected)

Msg 515, Level 16, State 2, Procedure uspPaulTest, Line 10
Cannot insert the value NULL into column 'Number', table 'Test.dbo.PaulTest'; column does not allow nulls. INSERT fails.
The statement has been terminated.(1 row(s) affected)

Number
-----------
1
(1 row(s) affected)

Visual Studio 2008 Errors

Recently I had my VS 2008 RTM/RC installation go pear shaped. Overnight I stopped being able to create VC++ smartdevice projects with the error

“Creating Project '<ProjectName>'... project creation failed”

‘No problem’, I thought, ‘I’ll just run setup and do a repair’.  Unfortunately, I got an error with the setup of

“A problem has been encountered while loading the setup components. Canceling setup.”

Luckily I came across a post on StackOverflow (which is running turning out to be a useful resource from the author of CodingHorror.com) pointing to a Microsoft tool to ‘force’ uninstall various Microsoft products, including Visual Studio:

http://msdn.microsoft.com/en-us/vstudio/bb968856.aspx

SQL Server 2008 Truncating Log File

First find the filename labels for your database:

select name from <DatabaseName>.dbo.sysfiles

Next truncate the log:

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

Finally shrink the database based on the log file in step 1:

DBCC SHRINKFILE('NameAbove_log',2048)

2048 is the resultant file size in MB.

Screen Real Estate Wastage

Usually I’m a fan of Microsoft UI design, however having installed the latest version of Windows Live Messenger (or more accurately had it forced on me) I’m astounded by the amount of screen real estate squandered.  Clearly Microsoft have been buying too many 24” monitors.  After turning off as much of the unneeded fluff as possible I was still left with a window far larger than necessary, with no real benefit.  See below!