EXCEPT to compare output of two queries

Very helpful SQL Server 2005+ commands:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx

I had to find the different rows of two pretty complex, non-related queries, and decided to look up if there was an easier way to do than by exploiting UNION (a technique I think I picked up from the same author, actually...). Turns out you just need to put and EXCEPT in between the queries. That's it.

I just found this last week and have probably already used it five times. It makes me want to get a book on SQL Server 2008 to see what other time-savers I can find, especially I'm analyzing LOTS of data these days.

Quick way to see what SQL data was updated

I had a crazy update query I was trying to troubleshoot, and for the life of me could not figure out why it insisted on updating some rows after they had already been updated by a prior run. Here's the quick one-time-use trigger I came up with to see exactly what was updated:

CREATE TRIGGER dbo.ViewUpdatedRecords
   ON  (table name)
   AFTER UPDATE
AS
BEGIN
    SELECT *
    INTO UpdatedRecords
    FROM INSERTED
END
GO

It only works one time, as SELECT INTO creates a table and will complain if a table by that name already exists. But it only took a minute to write, and helped me solve my issue very quickly.

New DVR, finally

I've finally put together a DVR out of spare parts. I built one before (and even got mentioned in Wired magazine), making my regular desktop do double duty, with a wireless 802.11B connection to the living room. On the TV side, a small Linux box (Hauppauge MediaMVP) decoded the video and handled the remote. It worked well about 95% of the time, but when it didn't work, it was a nightmare to troubleshoot. And you couldn't use the microwave, talk on the phone, or look funny at it... It also took me a month to build - mostly spent researching and tuning my network connection and figuring out my way around FFMPEG to compress the video.

When we moved to another house, it just wouldn't work reliably anymore, no matter what I did. I ditched the project for awhile, but picked it back up again when I got a free 10-year old computer - a Celeron 733Mhz with 64MB RAM. This was more an exercise in getting a slimmed down version of Linux (Mythbuntu/Xubuntu) to work more than anything else. I was able to get it to record shows, but not play them :-)

A few weeks ago, my neighbor gave me an old Dell 4300 1.8GHz machine. It wouldn't boot, but I found out that all it needed was a new power supply. I put an nVidia 5200 128MB card in it, a new quieter fan, and 1GB of RAM that I had saved from another dead computer, and it runs XP Media Center 2005 great (as long as you buy the $20 nVidia PureVideo software - not sure why this is required, but it is). It even runs Hulu perfectly (if you set it to medium quality) via a browser, but it chokes using Hulu desktop for some reason.

So now my kids are happy b/c they always have their PBS shows on tap, and my wife is happy, as she can watch her CBS shows. And this one only took me a few hours to set up. The thorn has been removed from my side.

SQL job retry (with additional logic) on fail

Here's some code I put together to get a SQL Server 2005 job to retry on fail. I spent a while looking for how to just have the job scheduler retry on fail, and found nothing. Of course, right after I coded and tested this, I found out a way to easily retry individual steps on fail, which is probably the way you want to do it 95%+ of the time :-), but just in case someone has the need to retry a job programatically and insert additional logic, or have more control over the process, or just wants to see how it could otherwise be done (ok, maybe at this point I'm just trying to justify wasting 20 minutes of my life coding this manually...), here's the code:

USE msdb

IF(
SELECT COUNT(*)
FROM sysjobhistory
WHERE
-- status 0 = error
run_status = 0
-- isolate the step we're looking for
AND step_name = '(job outcome)'
-- job unique ID (find this out by going to the job properties, and selecting "Script")
AND job_id = '48F4EE80-62C7-4DFF-8F6F-D009F616DA07'
-- today's date in the same style as the job history table format
AND run_date = CONVERT(VARCHAR(8), GETDATE(), 112)
) > 0
BEGIN
-- insert additional logic here

EXEC sp_start_job @job_name='[JOB YOU WANT TO RETRY]'
END


And here's the easy way:
Right-click on the job, choose "Properties". Click on "Steps", and select the step you want to retry. Click on "Advanced", and set the "Retry attempts" and "Retry interval". Repeat for additional steps. Done. D'oh!

Back to Windows

I'm back on Windows after a few months of using Linux exclusively at home. I still like Linux quite a bit, and am running a few distros in virtual machines now just so I can keep my skills up (and to support relatives who I switched to Linux - more on that in a bit), but I switched my main OS back to Windows because of the lack of usable audio and video tools, which I use all the time.

Not sure if "usable" is really the right word, but for me it's just so much more of a pain to work with the audio and video tools I found on Linux that were supposedly "best of breed" (Rosegarden and Ardour for audio, Kino and PiTiVi for video. I tried to get the Cinelerra and the highly-rated OpenShot video editor to work, but ran into lots of issues.) The audio tools were very difficult to set up, and the interfaces are nowhere near as slick as Mackie Tracktion 2 for Windows. Kino and PiTiVi were a total joke.

I initially bought into the idea that since it was open source software, I could fix and improve the software I wanted to work with. But the IDEs that I tried are nowhere near the level of VisualStudio, the languages they were written in I was unfamiliar with, and I just don't have the time to learn right now. Never mind that coding for audio and video applications is difficult to begin with. I might get into this someday, but I didn't want to deal with it now (I usually LOVE this type of stuff).

I did have great successes with converting two of my computer virus-prone relatives to Linux. One works for a major financial institution that has web software that won't work without the Microsoft JVM! Also, Windows security updates wreak havoc on the software. My solution was to install Ubuntu, and use Sun's VirtualBox with a working snapshot of XP. If he ever got a virus, he could just revert back to the snapshot. The financial software now works perfectly, and for the first time in years, he actually like using his computer. I also put my dad on Linux Mint, and he keeps telling me how much he loves it (aside from a few minor annoyances, but nothing like dealing with viruses and malware on Windows).

To be fair, both were using Windows XP - another virus-prone relative who I do tech support for has been on Windows 7 for a few months now (protected primarily with Microsoft Security Essentials), and has not had an issue yet, and loves her computer now as well.

There were initial hardware compatibility and other issues with both of these installs that took a bit of time for me to work through, but both systems are rock-solid stable now. I think Linux can work very well if you are just primarily browsing the web and just need to work with Office-type software (Sun's OpenOffice works very well if you're not doing anything crazy). It can also work well in other situations, depending on your exact needs.

Having switched back to XP myself, I find myself missing a few of the nice features on Linux now. The Bash shell is wicked powerful and a quick way to do system administration, having multiple virtual desktops is great, and I love how configurable pretty much everything is. Also, not having to worry about viruses nearly as much is awesome. I'm sure I will try it again someday.