Thomas Williams

Goodbye DNJ

thomasswilliams — Mon, 30 Jun 2008 02:53:00 GMT

After 4 years, nearly 200-odd posts, dozens of helpful comments and plenty of fun times, I'm leaving this DotNetJunkies blog.

Don't worry - my high-quality, Australian-influenced, caffeine-fueled blogging will continue at my new home on TheRuntime. Jay Kimble got me all set up and has been great all round (hi Jay!)

My new feed is at http://feeds.feedburner.com/thomasswilliams-tech

I'm leaving because in the end, the blogging side of DNJ was not kept up-to-date, e-mails to the admins went unanswered, and generally the blogs fell into disrepair IMHO.

Goodbye DNJ.

Tags: goodbye, theruntime, blogging

Quick Tip: Retrieving Report Definitions from the Catalog Table in the ReportServer Database

thomasswilliams — Thu, 12 Jun 2008 04:05:00 GMT

The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format.

I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job:

SELECT  [Name], 
        CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML 
FROM    ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK) 
WHERE   --get only reports, not folders or other resources 
        [Type] = 2

I would not recommend running this against the entire table. I've used the NOLOCK hint to avoid locking up the table, and also screened our non-reports by adding Type = 2 to the where clause.

I originally found this great little snippet by at the SQL Server and XML blog. Thanks Jacob! It came in so useful that I thought I'd write it down for later.

Tags: sql server, reporting services, database, xml

Remote Controls and Design

thomasswilliams — Fri, 06 Jun 2008 03:22:00 GMT

I'm the proud owner of a Logitech "Harmony" remote that controls the TV, DVD, and sound system in my lounge room. I'm not surprised at a recent (Logitech-sponsored) European survey that I read about at Jasper van Kuijk's blog which points out:

in 25% of homes, there is only 1 person who knows how to operate all the technology
87% of homes have three or more remote controls
49% of homes have five or more remote controls

I can openly say that my Logitech remote was one of the best tech buys I've ever made. It's key feature is that it groups commands around "activities". Click the "Watch a DVD" button and it switches on the DVD player, sets the sound system to input from DVD, and switches the TV on to the right input. Click on the "Watch TV" activity and the TV and sound system are set up correctly. And best of all, click "Off" and whatever is switched on, gets switched off. So easy even the kids can use it!

Tags: design, remote control, logitech

Book Review: Inside Microsoft SQL Server: 2005 T-SQL Querying, by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka

thomasswilliams — Thu, 05 Jun 2008 02:28:00 GMT

The first thing I need to say about this book is: wow.

T-SQL Querying is easy to read and yet really gets in depth with T-SQL. It reminds me of Itzik's presentations - complex concepts explained in a comfortable and informative style. The book covers query processing and optimisation, has some useful scripts for performance monitoring, logic puzzles, the new features of SQL Server 2005 like CTEs, ranking functions and APPLY, and plenty more. Some sections of the book could easily become my standard reference material on logical and physical query processing, while other sections are almost tutorial-like.

One of the advantages of reading this 600+ page book on paper over blog posts, is that a whole chapter can be dedicated to explaining a concept using increasingly complex examples. Another advantage is that most sections of code are followed by the corresponding results, which are in turn often followed by query plan results, so you can see the input, output and plan without necessarily having to run the query. In a blog post, this would just take up too much room.

I've already been able to put some of Itzik's hints and tips to good use, as well as finally using CTEs and understanding the "new" DMVs (things I knew about before, but didn't quite "get").

This book gets 2 thumbs up, 5 stars and 10 out of 10 from me. I guess next on my reading list will be the companion book Inside Microsoft SQL Server 2005: T-SQL Programming.

Tags: sql server, review, Itzik Ben-Gan

A Cheap, DIY way to document SQL Server databases

thomasswilliams — Wed, 28 May 2008 00:08:00 GMT

I've always had a problem with documentation. I agree it's absolutely necessary; it's just often the requirements for documentation are so vague that I never know if I'm done or not.

One way to enforce standards when documenting tables and views in SQL Server is by using extended properties and auto-generating the documentation. I've used many free tools to do exactly this over the years; currently I favour SQLSpec.

If you're interested in a cheap, do-it-yourself method of documenting SQL Server databases, check out this article titled "Create a SQL Server Data Dictionary in Seconds using Extended Properties" at MSSQLTips. Although I haven't tried it, it looks like a helpful article if you're into customising the output of your documentation.

Tags: sql server, documentation, extended properties

Adding Object Permissions to SQL Server 2005 Script As...Create Right Click Menu

thomasswilliams — Mon, 19 May 2008 03:00:00 GMT

One annoyance I have with SQL Server 2005's right click "Script As...Create" is that permissions on the object(s) to be scripted are not included by default.

You can change this in SSMS by going to Tools, Options and then selecting "Scripting" from the tree. There are lots of options for changing how scripts are generated - see this MSDN entry for the full rundown on scripting options.

To add permissions to the "Script As...Create", scroll down to "Object scripting properties", and set "Script permissions" to "True". Done!

Now if only I could get a DROP in the script before the create...

Tags: sql server, script, management studio

OT: Comment Spam!

thomasswilliams — Wed, 30 Apr 2008 03:28:00 GMT

Anyone else on DNJ feeling the pain of comment spam?

Maybe I'll take some freerolls or tramadyne or applesauce :-)

Anonymous comments are turned off for now. Use the contact form instead, please.

And can someone at the top fix this?

Tags: comment spam, DotNetJunkies

What are SQL Server Agent Job 'Categories'?

thomasswilliams — Wed, 30 Apr 2008 03:09:00 GMT

What does the "Category" drop-down for SQL Server Agent jobs do? The options are so limited and make no sense to me: "Full-Text"? "REPL-Alert Response"? "Web Assistant"?

If, like me, you've ever asked yourself the above question, wonder no more. Tim Ford has an article on MSSQLTips.com titled "Custom job categories to organize your SQL Agent jobs" which answers this, and more.

Tim's article is so useful and simple that immediately after reading it, I went out and added a couple of custom categories, and then assigned my "[Uncategorized (Local)]" jobs. All this took less than 10 minutes, and now I know about SQL Server Agent Job categories. Thanks Tim!

Tags: sql server, sql agent, Tim Ford

A Short Rant on One Example of Why Over-Engineering Stuff is a Problem

thomasswilliams — Thu, 24 Apr 2008 03:12:00 GMT

We've got a boiling water unit in the tea room that I pass by every day, that is over-engineered. I feel a short rant coming on...

This boiling water unit's primary function is to deliver boiling water, so people can make their instant coffee or tea. It does this well - there's a big black button that, when pushed, runs boiling water from a nozzle directly underneath the button. So far, so good.

If the boiling water unit was limited to this functionality, it could be regarded as a success. However, it also has:

an screen with the time and day of the week
four nameless touch-sensitive buttons (which are very cool - all you have to do is hover your finger near them and they flash red...but what do they do?)
a red "lock" LED
a switch that has a label "Lock", that is miles away from the LED

Why this over-engineering is a problem is that because no-one needs the time or day of week, no-one has set it. So now it's wrong. Further, there's no clear way to set it (unless you can find the manual). Why have the touch-sensitive buttons if they don't do anything? And why not have the "lock" switch in a visible location, and ditch the LED altogether?

Whoever made the boiling water unit could simplify it by removing the cruft, and so actually improve it. End rant :-)

Tags: design, water, engineer

Microsoft Course 2780 Maintaining a Microsoft SQL Server 2005 Database at Solid Quality Learning

thomasswilliams — Mon, 14 Apr 2008 17:30:00 GMT

Recently, my boss paid for me to attend Microsoft Course 2780 Maintaining a Microsoft SQL Server 2005 Database at Solid Quality Learning (thanks, boss!)

Around 18 months ago, our department was put in charge of a SQL Server 2005 box. I've been using SQL 2005 since then which of course gave me a huge headstart in the course. Way back when, I attended the 2779 training (focused more on developers), and thought that the 2780 training would complement it and make me into a DBA :-)

Well, the training is over, and I'm not a DBA, but I got a lot out of the course; below is my quick review on the good, the bad, and the ugly:

The Good

I would recommend this course if you are trying to come to grips with the DBA side of things in SQL Server 2005.
Rob Farley did a fantastic job standing up front of the class. He was knowledgeable and friendly, happy to answer questions and go "above and beyond" the standard course material.
I got to talk to a bunch of smart guys, like Greg Linwood, Brett Clarke and Simon Gerada from Solid Quality Learning.
The other attendees were the most polite IT professionals I've ever met. Everyone got along, and the class atmosphere was relaxed.
There was real food from a real cafe.
Our machines were P4 2.8GHz with 3.5 GB of RAM, running Windows XP, and all course work was done in pre-configured virtual machines. I had no technical hitches (Vista would have been nice, though).
Rob mentioned that originally the course was too short at just 3 days. 5 days was about right for the 9 modules covered.

The Bad

Um, not much here. Possibly too much typing of SQL as opposed to using the GUI :-)

The Ugly

The price - I realise we're actually paying for having an expert tutor and not the sum of all the good things like the PC's, lunch, and course book...but why is IT training so darned expensive?
Documentation - The standard MS coursebook is overly verbose, and does not feature a single screenshot. You could never sell a book like that.
Only instant coffee (unless you went to the cafe downstairs) :-(

All in all, this was a great training that a got a lot out of. Rob added real-world hints & tips, and it was also interesting to hear the other participants tales of managing multiple (several hundred, in one case) servers and different versions of SQL Server.

Tags: sql server, database, training, Solid Quality Learning, Rob Farley

I Don't Update Java Because...

thomasswilliams — Thu, 10 Apr 2008 13:22:00 GMT

...the updater is more invasive than the waft of a poopy nappy in a small room :-)

However, remembering to update software is not my strong point. gHacks put me on to a small program called JavaRa that checks for Java updates and removes old versions. And doesn't require an install. Very handy.

Time to go change that nappy!

Tags: java, updater, javara, ghacks

Why I Finally Switched From Bloglines

thomasswilliams — Tue, 08 Apr 2008 02:15:00 GMT

Bloglines was my RSS reader of choice for almost 4 years. I loved using it because it was web-based, easy to use, fast, dependable, full-featured (in 2004), and free.

Unfortunately Bloglines was ignored for a couple of years by the developers, during which time I stuck with it doggedly anyway. It got slightly less dependable (i.e. the notorious "Bloglines plumber"), and other readers overtook it in terms of functionality. Surprisingly, it was the new beta version which eventually drove me away - it was a case of too little, too late - and worryingly, the beta initially didn't work properly for IE which made me go back to the "classic"/2004 version.

Soon after, I decided to go looking at the various free readers on the market.

Google Reader is web-based, easy to use, fast, free, and has plenty of features to boot. It's even better than the Bloglines beta, and there's little worry about Google suspending development. My OPML imported easily, and I started reading feeds straight away...using it is almost enjoyable.

Some features that stand out on Google Reader are the speed, the ability to find new blogs which is almost too simple, offline viewing (~~IE only at the moment~~), and editing feeds and re-ordering them is a cinch.

YMMV - I need free and web-based, I'm not fussed by Digg or any "social" features, and the reader absolutely must work with IE and Flock. A good round-up of feed reader alternatives can be found at Wikipedia.

Goodbye Bloglines, may you live a long and healthy life, and perhaps we shall meet again.

UPDATE: Matt tells me that offline viewing works fine in Firefox. Fixed (thanks Matt)!

Tags: reader, rss, bloglines, google reader, switch

2 Code Snippets from SQL Server Training In Melbourne

thomasswilliams — Wed, 02 Apr 2008 03:46:00 GMT

I'm very fortunate to be at SQL Server 2005 training in Melbourne all week this week. Our instructor is Aussie SQL Server MVP Rob Farley who blogs at msmvps.com/blogs/robfarley/ and is unbelievably knowledgeable about the product (of course).

So far I've collected 2 useful code snippets from Rob - more on useful stuff from the course later:

--show all plans in the cache, courtesy Rob Farley
--adding "OPTION (RECOMPILE)" means that subsequent executions 
--of this query aren't added to the cache
SELECT  * 
FROM    sys.dm_exec_cached_plans p CROSS APPLY 
        sys.dm_exec_sql_text(p.plan_handle) t CROSS APPLY 
        sys.dm_exec_query_plan(p.plan_handle) q 
OPTION (RECOMPILE) 

--comma-separated from resultset in 1 call, courtesy of Rob Farley
--here we'll return all database names in a comma-separated string
--the trick is to give no column name (in the inner select) and no
--argument to XML PATH
--STUFF replaces the first comma with an empty string
SELECT  STUFF((
	SELECT  ',' + name 
	FROM    sys.databases
	ORDER BY name
	FOR XML PATH('')
), 1, 1, '')

Enjoy!

Tags: sql server, database, training, snippet

Xobni for Outlook

thomasswilliams — Mon, 04 Feb 2008 04:35:00 GMT

Just after Christmas, I got onto an Outlook plug-in called Xobni ("inbox" spelt backwards) (via Lifehacker).

Xobni adds a new sidebar in the main Outlook window which provides a wide variety of relevant information on the sender of the currently-selected e-mail. This information includes the number of e-mails you've received from that contact, contact details, who else they've CC'd or been CC'd on in all e-mails indexed by Xobni, quick links to their most recent e-mails, and a list of files sent to or from that contact.

I've used Xobni for only about a week and a half as I've been on leave - but already love the functionality given that I use Outlook all day. I appreciate little features like transition animations and neat, informative graphics which make for a very polished interface. The Xobni search is a step up from normal searching too (but too complicated to describe here).

Xobni is currently in beta and can be signed up for at http://www.xobni.com/, although you might have to wait to be able to download it.

Tags: xobni, outlook, windows, software, beta

Use Missing W3C CSS Syntax in IE 5, 6 or 7

thomasswilliams — Tue, 08 Jan 2008 01:04:00 GMT

I don't do much web development, but when I do I'm often amazed that Internet Explorer still seems to disregard some elements of the CSS standards as outlined by the W3C.

To address this shortcoming, Dean Edwards has written a small, optimised Javascript library to make IE versions 5, 6 or 7 "behave like a standards-compliant browser". It makes IE understand various things like:

pseudo classes
parent > child
adjacent + sibling
attribute[value] - cool!
max-height, max-width
the "peekaboo bug"
and lots more...

Check out Dean's test page if you use IE but long for more. Or have all the developers that care about standards compliance recommended that their users switch over to Firefox :-)

(Via Ajax Rain)

Tags: web, standards, css, ie, development