Thomas Williams Tech Blog

I came across a really interesting audit trigger for SQL Server 2005 a little while back, and thought it was worth re-posting it here.

The trigger basically logs changes for only columns that are affected into a generic audit table. It's got some fancy bit manipulation coding (that I still don't fully understand) and uses dynamic SQL, but hey, since I normally stay away from triggers anyway, I can accept a little dynamic SQL :-)

The alternative is to have an audit table that closely resembles my real table and log changes to that. And then run queries to find what was changed, and when. And then do this for every table I want to audit. And then...you get the picture.

You can find this amazing trigger at Pop Rivett's SQL Server FAQ No.5: Pop on the Audit Trail. It met my needs perfectly, but it may not meet yours: it's worth testing the impact of dynamic SQL and the effect if you use @@IDENTITY after inserts.

Links: http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

Tags: sql server, audit, log, trigger

I've been holding off upgrading my Subversion repository and tools, until last week or so when the "perfect storm" of source control arrived: VisualSVN Server with Subversion 1.5, TortoiseSVN 1.5, and the last piece of the puzzle, AnkhSVN 2.0.

If, like me, you've been waiting for the right time to upgrade your Subversion source control to the newly-released 1.5, then the free VisualSVN Server is one official (and dead-simple) way to do this as CodeBetter's James Kovacs recently noted. My older 1.3 repository was upgraded painlessly using VisualSVN Server, although I could have also called svnadmin upgrade from a command line as per this advice.

To Watch Out For: One point of confusion for me was the fact that I was running SvnService, installed with "Subversion 1 Click Setup" years ago, which still used the old Subversion binaries. I stopped the service, removed it and then renamed the old "Subversion" folder so that I could use the 1.5 binaries shipped with VisualSVN Server. VisualSVN Server doesn't add the path to the Subversion binaries to your environment variables though - this is one step you might have to do manually.

TortoiseSVN is a Subversion front-end for Windows Explorer. I've used it for years and have just upgraded to the latest 1.5 version, after upgrading my repository. The new version offers better icons...and of course much more (!), in addition to the existing integrated spell-checking, diff and merge tool, icon overlays, support for all Subversion protocols, etc. My "better icons" comment, while tongue-in-cheek, is actually serious: files not under version control or that are ignored are marked with different icon overlays, which is very handy.

Although I'm happy with TortoiseSVN, I wanted to include AnkhSVN 2.0 in my toolkit for its Visual Studio integration. I've been reading the Subversion blog and noted that AnkhSVN 2.0 offers full integration with Visual Studio 2005 and 2008. It certainly worked as advertised for me, installing simply enough and working immediately without any mucking around.

Good luck with your own upgrade - you'll have to decide for yourself whether this latest "perfect storm" of synchronised releases is worth upgrading to. And may your experience with the latest Subversion version and associated tools be as good as mine!

Links: VisualSVN Server http://www.visualsvn.com/server/, TortoiseSVN http://tortoisesvn.tigris.org/, AnkhSVN http://ankhsvn.open.collab.net/

Tags: subversion, source control, tools

SQL Bits is having a logo competition, and I entered the following:

My goal was to feature the familiar (often yellow) database cylinder, with simple text. I don't expect to win, but given the prize (an XBox 360) I thought it would be worth a try :-)

Links: http://www.sqlbits.com/LogoCompetition.aspx

Tags: sql bits, logo, contest

My mother-in-law Marijke gave me a special surprise the other day: a hand-me-down copy of the 1984 tome "Choosing and Using Your Home Computer: An Introductory Course".

Marijke had bought it some time ago to learn about computers. The book is a classic, from the time when PC's were called "microcomputers". It covers the latest hardware available at the time, the BASIC language, accessories like printers, plotters and the new-fangled "mouse", sprite graphics, games and the business case for computers.

What makes this book more poignant is that I was into computers in 1984. I had a Commodore 64, and one of my best mates had its predecessor, the Vic 20. I remember peeking and poking!

I'll post some more pics later, but for now enjoy the front cover and coverage of the "joystick":

Tags: book, review, history

I'm Thomas Williams, and I blog about SQL Server, Reporting Services, .NET, and development in general.

My old blog at DotNetJunkies was in desperate need of updating - thanks to Jay for setting me up here at my new home! Please subscribe to TheRuntime main feed or my personal feed to make sure you stay up-to-date with my posts.

More about me

I work in the health industry in Frankston, Victoria, Australia and live with my beautiful wife Olivia and 3 young kids. I am an MCP (going for MCAD.NET) and a MCTS in SQL Server 2005.

My full-time job involves writing software to process large amounts of data for enterprise data sources, using SQL Server 2005, SSIS, Reporting Services, and windows and web front ends (this is what I find myself blogging most about too, surprisingly).

I have a personal blog at Thomas Williams.blog, and I also enjoy movies, reading, board games, XBox, and some non-geeky things as well :-)

Tags: about, thomas williams, personal

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

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

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

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

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