Thomas Williams

Goodbye DNJ

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

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

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

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

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

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!

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'?

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 For