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.

Xubuntu 9.04 running smoothly on a 1999 iBook

My friend Shaun gave me an old iBook to play around with, and after messing with it for about a week, I gave up on it. It was incredibly slow, especially with networking. I looked online and did lots of optimizations, and got it to run a bit faster, but it was just painful to use. Since I didn't have the OS reinstall CD, I figured I was out of options.

About a month ago, I started playing around with Linux, and am really getting into it now. I did a few searches and found that there's PowerPC builds of Ubuntu. Given the modest hardware specs (300MHz G3, 192MB RAM), I decided to try Xubuntu 9.04 (alternate install). Xubuntu doesn't have all the slickness of Ubuntu, but it usually works really well on old hardware (I've got it zipping along on an old 700MHz PIII box w/ 192MB RAM, that functions as my DVR (using Mythbuntu)).

I'll give a rundown of the problems I encountered below (mostly related to the iBook itself, not Xubuntu), but the bottom line is after ~3 hours, I have a very decent-performing and very usable laptop now.

Problems
- The CD drive wouldn't recognize a CD. I reset the PRAM and shot come compressed air onto the laser. Holding the "C" key down on boot still didn't work, but holding the "Option" key down gave me a boot menu - the CD took a while to show up, but it eventually did.

- The battery wasn't holding a charge. There were two problems here - the power cord was a bit frayed and had to be positioned just right to work (temporarily solved with electrical tape), and the PMU needed to be reset.

- Wireless wouldn't work. Wireless issues Ubuntu turned me off of Linux two years ago, but I was hoping that since this was a PPC build, that they would have the Apple Wireless stuff figured out. And they do, except that Xubuntu didn't support the wireless encryption scheme I was using. Fortunately, this was an easy fix - just install and configure wicd (it's in the repositories - no need to download it from the web site).

Done - working laptop. I kind of feel bad having deMAC-ed a Mac, though (I was a HUGE Mac fanboy back in the day). Maybe I'll put a dock application at the bottom of the screen as an homage :-), or just go all out and do this.

UPDATE: Just found this helpful link that lists all distros that support the PowerPC architecture:

http://distrowatch.com/dwres.php?resource=ppc

Moving to Linux, at least temporarily

Something was up with my Windows install, and for the life of me I couldn't figure it out. The sound was very choppy, Windows startup time and Internet browsing were incredibly slow. I ran every virus/spyware/malware scanner I could think of, cleaned the registry out carefully with CCleaner, reinstalled my sound card drivers, removed always running apps that I didn't need, defragmented the hard drive, defragmented the page file and registry hives, etc, and still had the same problem.

About to reinstall Windows XP on my 10-year-old computer, I decided to give Ubuntu another shot, pledging to boot only Ubuntu for a week, and attempting to fix whatever issues came up. I had tried it at v7.04 and was generally impressed, but eventually scrapped it mostly because Windows was working fine for me, my Digital Audio Workstation wasn't supported, and there were no "killer apps" I could think of to keep me on Linux.

So I installed Ubuntu 9.04, and was about as impressed as I was with 7.04. I worked through a few initial issues, but everyone in the forums I was reading said something like, "[whatever issue] isn't a problem on Linux Mint". After reading that for about the 5th time, I decided to make a Live USB of Linux Mint 7, and was completely blown away. Pretty much everything just worked out of the box, and the interface looked absolutely stunning and more user-friendly. What really sold me, though, was that it was based on Ubuntu 9.04, so it's got lots of built-in support.

So I wiped Ubuntu and installed Mint, and mostly haven't missed Windows at all. I still do have a full-screen video issue using the generic video drivers - the "nv" drivers work, but only give me a resolution of about 320 x 240. UPDATE: Installing the latest nv driver fixed this.

Most of the programs I want are available on Linux - Firefox, Photoshop (via Wine), Picasa, and Netbeans, mostly. But I can't find a DAW that supports MIDI to work. I spent quite a bit of time reading up on this, and it looks like I need a realtime Linux kernel, which can be installed with apt-get, but it seems like nobody can get it working with Linux Mint. I did find this video, but it looks very complex (though promising). I've got to spend a lot more time with it, but I may just set up a Windows partition with only my DAW on it.

The "killer app" for me so far is not having to really worry about viruses, spyware, etc. nearly as much (the computer is used by a number of people). I do have ClemAV running now, and am looking into whether or not I need more protection, but I like what I've read so far - that Linux out-of-the-box is leaps and bounds above Windows in regards to these things.

And my son LOVES the Linux Education games - GCompris and Childsplay. To my surprise, he was WAY into the Childsplay letter finding game, which is great because he's working on his lower case letters now. I can't wait to hack these games a bit and put in some customized graphics for the kids.

Quick SQL --> Excel export w/ column names

I know there's lot of ways to do this, including an SSIS/DTS package, but here's another quick way I pieced together for ad-hoc exports with that I haven't seen outlined before:

1. Run the query in SQL Server Management Studio (w/ "Results to Grid")
2. Copy all data from the grid (right-click on the unlabled box at the top left of the grid, choose "copy"), and paste into second row in Excel.
3. Back in SQL, run this query:

SELECT sc.name
FROM syscolumns sc
   INNER JOIN sysobjects so
       ON so.id = sc.id
WHERE so.name = '[table name]'
ORDER BY sc.colid

4. Paste the results into Excel in a new sheet.
5. Copy the row, then "paste special" --> "transpose" into the first row in the original worksheet, so that they paste as columns.

This will probably break down for very large tables, but I just did this with 96 columns, and 10,000+ rows without issues.

Some more ways to accomplish this:
http://www.mssqltips.com/tip.asp?tip=1202

BTW, not sure why there's not a "Results to .csv format" in SQL Server Management Studio. "Results to Text" seems rather useless...

UPDATE: Dave Kearns points out an even easier way to do this:

In SQL Server Management Studio, go to "Tools" --> "Options" --> "Query Results" --> "SQL Server" --> "Results to Grid" --> check "Include column headers when copying or saving the results"

Forcing label values to be displayed to the right of a bar (ASP.NET 3.5 chart control)

As I've previously written, the new ASP.NET 3.5 chart control is great. However, a minor thing that doesn't work well is displaying value labels for bar charts. It displays the value to the right of the bar except for the longest bar when it determines that it wouldn't fit within the scale. So, if you have a bar with a value of 98, and you have your axis interval set to 25, it will display "98" in the bar. This doesn't necessarily look good, depending on what color the bar is, and it is also inconsistent with the other bars.

Here's my solution (C#):

Chart1.DataBound += delegate(System.Object o, System.EventArgs ea) {
      double longestBarLength = Chart1.Series[0].Points.FindMaxByValue().YValues[0],
             padding = 10d,
             roundedUpBarLengh = Math.Ceiling((longestBarLength + padding) / 25) * 25;

      Chart1.ChartAreas[0].AxisY.Maximum = roundedUpBarLengh;
};

I'm determining the longest bar length w/out looping thanks to a nice built-in function "FindMaxByValue()" which gets me access to the DataPoint object with the largest value, and I'm just grabbing the Y Value from it (in my case, there's only one Y value).

Then I specify a set padding to make room for the value label to be displayed on the right side of the bar (10 seems to work well for numbers in the 100's at least). Lastly I'm pushing the value whatever the longest bar length is to the nearest 25 and setting the axis max value to that number.

This all needs to be done after the data is already there, obviously, so this is all wrapped in the databound event handler.

I'll add pics later.

Great .NET 3.5 Charting Control from Microsoft

I don't know how I missed this when it was released a few months ago, but this control is awesome - it generates very professional-looking charts that you used to have to pay hundreds of dollars for, and it looks just as good as the best charting tools I've seen.

The organization scheme seems a little convoluted to me, but it probably just seems that way because I was just trying to do a simple 3D bar chart, and the scheme is set up to allow a very high level of customization. I also ran into a few web.config-related snags while getting my code up and running, but nothing that I couldn't fix within a few minutes of searching the web.

Example screenshots and everything you need to get started are here.