I'm into Black Magic

I've been using this BlackMagic drive speed test program for OS/X lately. It's free on the App Store and quite handy. Missing some tests I'd like to see but still for free who can complain.

I thought I'd share some read/write drive speeds for various hard drives, USB devices, SSD drives, etc that I had. Note sure if anyone will find them useful but here they are regardless.

Lexar 8 gb USB thumb drive:

I had a cheap USB 2.0 thumb drive cost was $9.99 on sale, it was formatted with Mac OS Extended (Journaled).

I tried plugging it into my MacBook directly, and then into my wire Apple keyboard which is plugged into a USB hub in turn plugged into my Thunderbolt Cinema display that then connects via Thunderbolt to my MacBook. I thought it would be interesting to see the difference a 3 hop daisy chain made. I also tried plugging in a old thumb drive beside it I had that I believed was USB 1.1 to see if it would bring down the overall speed of the hub.

Test Performed	Write	Read
8GB drive (plugged direct into MacBook)	5.4 MB/s	20.6 MB/s
8GB drive (3 USB hops thru various hubs)	4.6 MB/s	11.9 MB/s
8GB drive (3 USB hops thru various hubs plugged in beside a suspected USB 1.1 device)	4.5 MB/s	11.9 MB/s

Conclusion

Well there was a difference, especially in read performance, this was expected but I was surprised plugging in the old USB 1.1 thumb drive beside it on the Apple keyboard didn't drag it down further. Perhaps that old drive was actually USB 2.0. It was too small to run the Black Magic speed test utility on to get numbers for it.

Seagate Momentous Hybrid vs stock Apple hard drive:

I bought a Seagate Momentous 500gb Hybrid hard drive. It featured a SATA II interface and used a 7200rpm hard drive married with a 4gb SLC Nand chip to promise SSD performance at HDD prices. I'd had one before in my old white 2007 MacBook and liked it so figured it would be a suitable replacement for the stock Apple drive. I particularly wanted a SATA II interface for the drive in the optical bay of my MacBook because there's stories of unreliability using SATA III devices in this bay, Apple's own Superdrive negotiates a plain old SATA 1.0 link so Apple's never claimed even SATA II support for that bay. 

I compared the Seagate Momentous to the stock Apple 500gb 5400rpm drive my MacBook came with.

Test Performed	Write	Read
Apple stock 500gb 5400rpm	53.1 MB/s	52.9 MB/s
Seagate 500b Momentous hybrid	99.2 MB/s	103.6 MB/s

Conclusion

Huge difference! These compare with the numbers I used to get on my white 2007 MacBook, the Seagate is double the Apple 5400rpm drive. I believe too the Seagate's NAND memory isn't having a huge impact on these numbers. It's a sequential write followed by a sequential read. Seagate doesn't write  the data to the NAND as per their spec sheets so the write is just the 7400rpm drive and the read only happens once in the BlackMagic test so it's coming from disk and also measures real disk performance. I believe real world use of the Seagate drive is actually going to be much faster since Seagate's adaptive technology can keep the most used content in that 4gb NAND.

SSD drive vs Seagate vs Stock 5400rpm

As you can expect the SSD is going to win. The SSD is SATA III with MLC NAND chips in the main bay that fully takes advantage of the SATA III 6gb/s link, the other two drives are SATA II devices and made of spinning platters of molasses.

Test Performed	Write	Read
Apple stock 500gb 5400rpm	53.1 MB/s	52.9 MB/s
Seagate 500b Momentous hybrid	99.2 MB/s	103.6 MB/s
OCZ Agility 3 SSD drive	139.2 MB/s	202.2 MB/s

Conclusion

Obviously the SSD wins but on write speed it would seem to not be leaps and bounds ahead. On read speed it clearly dominates. But the trouble is the test is showing the mechanical drives off at their best and the SSD at its worst. Mechanicals love sequential read/write tests, the data is lined up nicely on the platters and they can just scoop it up, they also don't care if the data is compressible or not, they write each byte 1:1 to the platters. The SSD though hates sequential writes and reads; due to how NAND works they much prefer random read/writes and with the SandForce controller in the Agility 3 if the data can be compressed they use that to their advantage and gobble it up even faster and write fewer bytes to the NAND as a consequence. So real world; reading data the stock drive is ~50MB/s best case, the Seagate is likely a fair bit better than 100MB/s and the Agility should be managing somewhere close to double the 200MB/s reported. I just wish I had a test to prove that.

Firewire 800 vs USB 2.0

I have a Firewire 800 and USB 2.0 ports on my Thunderbolt Cinema display so thought a test of Firewire 800 vs USB 2.0 would be fun. The Firewire 800 drive I have is made by Seagate but probably a nothing fancy drive, no specs are listed on the box for it. For USB I put the SSD drive into a USB 2.0 enclosure (of dubious Chinese manufacture). I tried various combinations of Thunderbolt/MacBook direct and even distinguished between having multiple USB devices plugged in vs a lone USB 2.0 device.

Test Performed	Write	Read
Firewire 800 with unknown drive (plugged into Thunderbolt Cinema display)	41.8 MB/s	25.5 MB/s
Firewire 800 with unknown drive (plugged into Macbook directly)	42.8 MB/s	26.7 MB/s
Firewire 800 with Seagate Hybrid (plugged into Thunderbolt Cinema display)	41.2 MB/s	33.0 MB/s
USB 2.0 with Agility 3 SSD (plugged into Thunderbolt Cinema display)	14.0 MB/s	31.7 MB/s
USB 2.0 with Agility 3 SSD (plugged into Thunderbolt Cinema display with a USB 2.0 hub plugged in beside it)	7.4 MB/s	12.5 MB/s
USB 2.0 with Agility 3 SSD (plugged into MacBook directly)	27.5 MB/s	33.7 MB/s

Conclusion

Wow! Thunderbolt does a good job offloading ports from the MacBook without losing speed. Unfortunately USB 2.0 shows it has a well known but fatal flaw that performance depends a lot on what's plugged in around it. Alone USB 2.0 and Firewire 800 weren't that far apart but plugging a USB 2.0 hub in beside the USB 2.0 drive murdered its performance. I even tried pulling everything from the USB 2.0 hub so it was JUST the hub and still got the same numbers.

I am glad to have the Firewire drive as because it's always isolated from the USB 2.0 stuff I have attached and can maintain it's peak performance. I can also see my Firewire 800 drive had better reads with the Seagate Hybrid which suggests to me my numbers for Firewire were not yet at their peak, I wish I'd tried the SSD in the Firewire 800 enclosure to really remove any chance of the hard drive being a limiting factor. 

Anyway it was fun and interesting to see what performance all these drives and access methods afford me. 

Upgrading my MacBook pro to include an SSD and HDD

I upgraded my MacBook Pro 15" 2011 this weekend to use a SSD and HDD in a dual drive scenario.

I am not going to attempt a complete guide, I used steps similar to this guide by Weldon Dodd who's already done an excellent job. I will outline my steps which I think are useful as most people (like me) won't have Weldon's scenario. Namely he did a clean OS install and only copied his user data, most of us are not willing to walk away from the apps and settings we have and expect to migrate that.

Prep Work

Please read a few articles before attempting this; it can be hard to search for stuff when your computer is torn apart. Some stuff I recommend getting familiar with first is opening your MacBook and putting the SSD bay in. Search YouTube, there's a lot of good videos walking you through the physical SSD install process.

Here's what I had to start
MacBoook Pro 2011 with a 5400rpm 500gb drive
A time machine backup (optional)
A 8gb usb thumb drive (needed)

What I bought to go into the unit
A drive mounting bracket for the optical bay in the MacBook
A usb external case to mount the optical drive in once pulled
A new 500gb Seagate Momentous 7200rpm hybrid drive (The HDD)
A new 120gb OCZ Agility 3 solid state drive (The SSD)

Tools I gathered in advance
A very small phillips screw driver
A torx T6 screw driver (these can be hard to find and you need one that's got a thin shaft)
SuperDuper for doing disk to disk backups.

Steps I followed

Here's the basic steps I used to do my upgrade. I don't have detailed notes for each step but if you are computer savvy I think you'll understand each thing I did.

1. Prepare an OS/X Lion installer on the 8b thumb drive. (Josh Sunshine has a guide on that)
2. Make sure your Time Machine backup is up to date. 
3. Turn OFF Time Machine. You don't want it trying to backup during your various steps, it'll get confused. 
4. Open the Mac and remove the SuperDrive and replace with the new 500gb (or use your stock one if you aren't going with a new HDD too. If you are going with stock skip to step 7).
5. Fire up the Mac and use SuperDuper to clone the entire stock hard drive to the new drive. This took a couple hours for me. I was getting speeds at first around 5mb/s which seemed dreadful then it climbed to 35+ eventually... either way 2 hours seemed slow.
6. Open the Mac and remove the stock hard drive.
7. Put the SSD in the main bay where the stock drive was. 
8. Close up the Mac and put the OS/X Lion thumb drive in before booting.
9. Boot with the Option key held and when prompted choose your OS/X Lion thumb drive.
10. Choose new Lion install and pick the SSD for a target.
11. After install completes choose option to copy from a hard drive and pick the drive in the optical bay.
12. Copy all the settings, applications, etc but or users DON'T copy the big folders that would overflow your SSD (skip Movies, Music and Pictures for instance). All the folders you skip are going to live permanently on the HDD. 
13. After it completes reboot as normal and login (you should be booting into the SSD)
14. For those folders you DIDN'T copy from the HDD go into the SSD user folder and replace them with symbolic links. (This is easier said than done as it's going to require working at the terminal prompt. Weldon Dodd has a great guide on that too). 
15. Any other users you didn't copy all the folders for will need to have symbolic links made for them too. 
16. Put the SuperDrive you removed into the external case.
17. Remove the system folders from the HDD, just leave the user folders and empty the trash. The trash will take 15+ minutes to empty but do it before the next step, it'll save some Time Machine backup time.
18. Turn TimeMachine back on, you'll need to probably point it back at the proper Time Machine volume if it's on a network. It will also want to do a full backup again unless you named your new SSD exactly the same as the old stock drive. When it's done Time Machine will show a colour change in the history time line at the point of the new SSD, but you can still move smoothly past that point and restore files pre-SSD upgrade.  
19. Done!

Notes and Tips

• In my case I reason to open the Mac, do some operations, boot the Mac and then shut down and open it again. When this happens don't bother putting all the screws in for the bottom cover. Just hold the cover in place, set the MacBook down normally and boot.
• You'll notice I mentioned Time Machine a bit. It's not actually "needed" during this guide, if you don't use Time Machine you can skip any Time Machine mentions......but you should also START to use Time Machine it's a great app.
• If you've done this right you'll actually have a tiny hidden recovery partition installed by OS/X Lion on the SSD for cases of failure. This partition is 1 GB (680mb used for me). I was happy to have it but you may not be. Using Disk Utility you could move it to a .dmg file, shrink your HDD to allow a 1 GB partition and restore the DMG there and recover that space. To verify it's there boot with Option held or check for "Recovery HD" under your SSD drive in System Report.
• Apple's options to boot and migrate data from various sources astound me. Booting with the Option held it offered to boot from my USB Thumb, a Snow Leopard thumb drive in my USB hub, my old HDD over Firewire (I had plugged it in), the recovery partition on the old drive and the new HDD. When I finished my fresh install and It reached out and spotted my Time Machine over wifi, my new HDD and my old HDD and offered to drag settings/apps/users from those sources. 
• OS/X on your MacBook is going to make a big sleep file on your SSD. This is 16gb space hog on my machine. By default if you sleep your MacBook it writes the memory to disk then sleeps. You can change the behaviour to instead just sleep and keep the memory powered, it can power it from a charged battery a long time (more than 7 days in my experience) this is faster to sleep, faster to wake and doesn't hog any SSD space. Chris Rawson at TUAW did a guide for changing the settings and clearing the sleep file. It's essentially two terminal commands and worth doing. I did it even before having an SSD as it makes sleep so quick. 
• My Agility 3 SSD uses a SandForce 2281 controller which some recommend leaving TRIM disabled for since it has it's own cleanup mechanism. If you don't have such a controller find an article on enabling TRIM in OS/X Lion. 
• Go into System Preferences -> Startup Disk and choose the SSD as the default boot drive. Even if it's the only drive shown you need to select it. This tells the OS to not check for USB and/or other drives at boot time and speeds up the boot performance. Took me down for 17 seconds to 13 seconds. 
• Your email accounts and all their mail is going to be on the SSD by default unless you moved your Library folder. If you wish it somewhere else (or even just specific accounts to be off the SSD) you can make symbolic links for Mail or even for each separate account to accomplish that. See this article by Heinz Tschabitscher that outlines the specific folders for mail. 

SSD Mounting Bracket

You are going to need to purchase a SSD mounting bracket. The two big vendors are OWC and Optibay. Both are expensive ($99 + shipping). eBay has many sellers selling very inexpensive versions of these. These bays are used in more than just Mac's and many of them will work but require altering the bracket if you don't order the right one. What to look for is a unit with two special screw holes near where the CD would eject.

I went with eBay and I've attached a picture of the one you want to buy and one you want to avoid. These pictures seem to be used nearly identically by all the eBay merchants selling these units. Make sure it mentions it's for the Apple Superdrive and MacBook.

When you do go to use the adapter make sure to remove the little metal clip from the back of the Apple super drive and add it to the new bracket (the new bracket will have holes pre-drilled for the Apple clip). On mine the pre-drilled holes for the clip were a little too tight but I realized it came with screws of it's own for mounting the clip so use theirs instead of Apple's for this clip.

This is the one to look for

I bought mine from a seller named "Rexchg" who had 70,000+ sales. Total cost was $12.90usd including shipping to Canada.

It arrived in a nice bubble wrap mailer with screws and little plastic piece that locks the SSD in place. Quality was really very good.

Shipping time from order to when received was only 8 days and it came from Hong Kong so that was pretty impressive.

Rexchg also sold me the external case for the old SuperDrive (see notes further down).

This is the type to avoid (no screws at front)

The units to avoid are for laptops without slot drives. They are often sold as being for SuperDrives or for MacBooks so be careful.

From what I read they will work but you need to bust off that plastic at the front and live without two of the hold down screws. They aren't any cheaper either.




Mounting the SuperDrive in an external case

From Rexchg on eBay I also bought a case to mount the old optical Superdrive and use it via USB. I tried to pick a case that would match the MacBook and figured it would also be nice to order it all from the same seller. Total cost for the case was $16 usd with shipping to Canada.

I picked the one shown. It's made of plastic not aluminium like the MacBook but it's perfect match for the style and finish of the MacBook. It requires no screws, the SuperDrive clicks in place and closing the unit you'll hear a couple clicks and it's locked in forever.

It works great, seems to be just what I needed, its USB powered with no option for external power. I checked the speed of the USB interface with a hard drive before putting the optical drive in and it was 34 MBs/ read and 27.5 MB/s write which seems normal for USB HDD devices I've tried.

Quality seemed flimsy to be honest around the slot opening but once I put the drive in it added a lot of structure to the plastic and it seems fine now.

I am totally impressed with how legit it looks beside the MacBook, I did scratch it with a knife as I cut open the bubble wrapper so be careful.

Ok, now how fast is it?

So I have used a disk speed program for the Mac called BlackMagic and gathered some speeds on a variety of devices prior to upgrade. On my old 2007 White MacBook with a Seagate Momentous 500GB hybrid I was getting ~100MB/s read and write using 1GB files (lower with 5GB files). On the new MacBook Pro with the stock hard drive I was getting 53MB/s read and write...yuck!!

I was pretty much expecting to get around 300+ MB/s reads on this new drive it's an OCZ Agility 3 with a SATA III interface and speeds up to 525MB/s are touted in the advertisements. Well I didn't, not even close. With BlackMagic I only got 202 MB/s read and 139 MB/s write.....tears ran down my face! I did some searches and found a smorgasborg of people with great speeds, slow speeds, people disappointed like me and people trying to get drives of all flavours replaced because they were too slow... hmmm. What's up with that?

Well after enough searching I figured out the situation. There's not a lot of choice for speed testing apps for hard drives on Mac's the best ones cost money and everyone's cheap. BlackMagic is free and popular, it's provided as a bit of advertising for a company (BlackMagic) that works in the video world and needs high performance drives to move raw real-time video streams. Their tests rate the results for various streaming video activities like 1080P 60fps 12 bit RGB raw feeds, etc. so they use a sequential read/write test of either 1, 2, 3, 4 or 5GB files.

BlackMagic originally used a test stream of data that was highly compressible, this worked fine for testing traditional hard drives but modern SSD's with the right controllers (such as mine) take advantage of compressible data and chew through it extra quick giving you even better performance. This isn't just a gimmick to get big numbers, most user data is quite compressible. BlackMagic realized their original tests were flawed for the kind of work they did; video data streams aren't very compressible and hence as of version 2.1 (maybe even 2.0) they use different test data that doesn't compress and removes that performance advantage drives like mine can give. This changes the results for many drives to much lower values since they can't get the compression boost, but people have posted numbers from both versions of BlackMagic with no regard for which is which. It's a land of confusion.

I went to OCZ's site and they publish (as do most drive makers) a sequential read/write test with random data (this data doesn't compress well). In those specs my Agility 3 drive should get 195 MB/s read and 130 MB/s write so I am actually beating those numbers and therefore quite happy now.

Now real world feel....it feels fast! Stuff loads instantly. My Windows 7 virtual machine in VMWare boots in 12.7 seconds. My MacBook boots Lion in 17 seconds (down from 45 seconds) [Edit: With the tip I added earlier in this post I was able to get boot time down to ~13 seconds by making the SSD my startup disk]. Anything I click that's on the SSD just pops open.

Worst part is when I load something that checks drives my internal HDD and external USB drive spins up which takes a couple seconds and while I never used to notice it, I do now. Grrr all hard drives must die.

I can also hear the Seagate 7200rpm drive when it's spinning (which I am surprised by, I couldn't hear the one in my old MacBook). Not a problem for me, but I was hoping for dead silence.

Convert T-SQL DateTime to strings using format masks like .Net supports

This T-SQL function is based off something I ran across somewhere on the web but don't recall where. I made it to support almost all the formatting masks and such that .Net supports with it's string formatting for dates and times. It's handy if Sql server doesn't  have a Convert() that fits your needs.

Normally formatting dates in the database isn't recommended but I've had cause to write procs that return XML and needed dates in specific formats. Works in SQL 2005 or SQL 2008, if you don't need to support 2005 there's some code commented out inside for time zones that's more efficient.

The code looks all mangled in this blog post, but cut and paste it into SQL Management Studio and it looks good. There's some sample calls near the bottom to give you an idea of the format mask styles supported.

-- ------------------------------------------------------------------------------------

-- This function supports date and time formatting using roughly the format masks supported

-- in .Net.

-- 

-- It supports most of the date/time formatting syntax. Notable exceptions are fractional seconds

-- and some of the timezone variants. It also doesn't support true abbreviated months it

-- just takes the leftmost 3 characters which works for English.

-- ------------------------------------------------------------------------------------

CREATE FUNCTION dbo.fnc_FormatDate (@DateTimeToFormat DATETIME, @FormatMask VARCHAR(50))

RETURNS nvarchar(100)

AS

BEGIN

    DECLARE @FormattedResult nvarchar(50)

    SET @FormattedResult = @FormatMask

    IF (charindex('YYYY', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'YYYY', datename(YY, @DateTimeToFormat))

    IF (charindex('YY', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'YY', right(datename(YY, @DateTimeToFormat), 2))

    IF (charindex('MMMM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMMM', datename(MM, @DateTimeToFormat))

    IF (charindex('MMM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMM', left(datename(MM, @DateTimeToFormat), 3))

    IF (charindex('MM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MM', right('0' + cast(datepart(MM, @DateTimeToFormat) as nvarchar(2)), 2))

    IF (charindex('M', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'M', cast(datepart(MM, @DateTimeToFormat) as nvarchar(2)))

    IF (charindex('DD', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'DD', right('0' + datename(DD, @DateTimeToFormat), 2))

    IF (charindex('D', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'D', datename(DD, @DateTimeToFormat))  

    IF (charindex('HH', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'HH', right('0' + datename(HH, @DateTimeToFormat), 2))

    IF (charindex('H', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'H', datename(HH, @DateTimeToFormat))

    IF (charindex('hh', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'hh',right('0' + cast((datepart(HH, @DateTimeToFormat) + 11) % 12 + 1 as nvarchar(2)), 2))

    IF (charindex('h', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'h', cast((datepart(HH, @DateTimeToFormat) + 11) % 12 + 1 as nvarchar(2)))

    IF (charindex('mm', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'mm', right('0' + datename(Minute, @DateTimeToFormat), 2))

    IF (charindex('m', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'm', datename(Minute, @DateTimeToFormat))

    IF (charindex('ss', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'ss', right('0' + datename(Second, @DateTimeToFormat), 2))

    IF (charindex('s', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 's', datename(Second, @DateTimeToFormat))

    IF (charindex('tt', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'tt', CASE WHEN datename(Hour, @DateTimeToFormat) < 12 THEN 'AM' ELSE 'PM' END)

    IF (charindex('t', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 't', CASE WHEN datename(Hour, @DateTimeToFormat) < 12 THEN 'A' ELSE 'P' END)

    IF (charindex('zzzz', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

    BEGIN

       -- If SQL 2008+ the line below can replace the code within this block

       -- SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'zzzz', right(cast(SYSDATETIMEOFFSET() as nvarchar(50)), 6))

      

       DECLARE @GmtOffset int

       DECLARE @GmtSign nvarchar(1)

       SET @GmtOffset = datediff(minute, getutcdate(), getdate())

       SELECT @GmtSign = CASE WHEN @GmtOffset >= 0 THEN '+' ELSE '-' END

   

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'zzzz', CASE WHEN @GmtOffset >= 0 THEN '+' ELSE '-' END + right('0' + cast(abs(@GmtOffset) / 60 as nvarchar(5)),2) + ':' + right('0' + cast(abs(@GmtOffset % 60) as nvarchar(2)), 2))

    END

   

RETURN @FormattedResult

-- SELECT dbo.fnc_FormatDate(getdate(), 'MM/DD/YYYY HH:mm:ss tt')

-- SELECT dbo.fnc_FormatDate(getdate(), 'MMM DD, YYYY hh:mm:ss zzzz')

-- SELECT dbo.fnc_FormatDate(getdate(), 'MMMM DD, YY HH:mm:ss tt')

-- SELECT dbo.fnc_FormatDate(getdate(), 'M/D/YY H:m:s t')

END

How to convert a CIDR IP address to a range in T-SQL

First pre-create this function in SQL Server (from http://www.stardeveloper.com).

CREATE FUNCTION [dbo].[ConvertIPToLong](@IP varchar(15))
RETURNS bigint
AS
BEGIN
    DECLARE @Long bigint
    SET @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
        CONVERT(bigint, PARSENAME(@IP, 1))

    RETURN (@Long)
END

This is a sample of T-SQL code I put together that will calculate the low and high IP ranges from a CIDR address. It's messy and I had to work around T-SQL's lack of bit shift operators.

Declare @CidrIP varchar(50)
Set @CidrIP = '10.100.60.55/28'

Select dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) as LowRange,
       dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)       

Execute SQLCMD scripts via a class in C#

Here's a utility class I put together so I could take the output scripts from a Visual Studio 2008 database project and execute them from code without calling SQLCMD.EXE. The obstacle to direct execution of the scripts is they contain many instances of syntax only available through SQLCMD. To my knowledge there's no direct ADO.NET way to use SQLCMD syntax in scripts.

Note: You may even find this useful for any SQL Scripts simply because it supports the GO statement that typical ADO.Net does not.

My class supports several of the most import SQLCMD Syntax bits and gracefully ignores the one's it doesn't support. It's not a perfect replacement for SQLCMD but gets the job done. This class has not been extensively tested.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;

namespace MyNameSpace
{

  
    /// <summary>
    /// Supports running a SQLCmd Mode style statement such as the output from a VS 2008 Database Team Edition Database Project
    /// Only a limited subset of the SQLCmd mode syntax is supported. Other gotchas.
    ///
    /// Uses a database transaction; if anything fails inside a On Error Exit group then the entire script is rolled back
    ///
    ///
    /// Supported Commands:
    ///     GO (note GO [N] will only be executed once not N times)
    ///     :setvar
    ///     $(MyVar)
    ///     :on error exit
    ///     :on error resume (only for SQL Errors)
    ///     :on error ignore (only for SQL Errors)
    ///
    /// The Following SQLCMD Commands are recognized but ignored. They will not crash your script if encountered but will be skipped
    ///     :ED
    ///     :Error
    ///     :!!
    ///     :Perftrace
    ///     :Quit
    ///     :Exit
    ///     :Help
    ///     :XML
    ///     :r
    ///     :ServerList
    ///     :Listvar
    ///
    /// The following SQLCMD pre-defined variables are pre-defined by this class just like they are by SQLCMD
    /// The only difference is SQLCMD actually used and/or updated these variable. This class simply has them predefined
    /// with much the same values as SQLCMD did. The class allows you to change ALL variables (unlike SQLCMD) where some are
    /// read only.
    ///     SQLCMDUSER ""
    ///     SQLCMDPASSWORD
    ///     SQLCMDSERVER {Server Name}
    ///     SQLCMDWORKSTATION {Computer Name}
    ///     SQLCMDLOGINTIMEOUT {Connection Timeout}
    ///     SQLCMDDBNAME {Database Name}
    ///     SQLCMDHEADERS "0"
    ///     SQLCMDCOLSEP " "
    ///     SQLCMDCOLWIDTH "0"
    ///     SQLCMDPACKETSIZE "4096"
    ///     SQLCMDERRORLEVEL "0"
    ///     SQLCMDMAXVARTYPEWIDTH "256"
    ///     SQLCMDMAXFIXEDTYPEWIDTH "0"
    ///     SQLCMDEDITOR "edit.com"
    ///     SQLCMDINI ""
    ///
    /// The following pre-defnined variables ARE used by the class and thier values when set are not ignored
    ///     SQLCMDSTATTIMEOUT "0"
    ///   
    /// One Additional Variable is defined so that scripts could potentially detect they are running in this class instead
    /// of SQLCmd.
    ///     SQLCMDREAL "0"
    /// </summary>
    public class ExecuteSQLCmdMode
    {
        private Dictionary<string, string> _variables; // Hidden
        private List<string> _lockedVariables; // Hidden
        private errorMode _errorMode; // Hidden
        private SqlConnection _sqlConn; // Hidden
        private List<string> _ignoredCommands;  // Hidden
        private bool _allowVariableOverwrites;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExecuteSQLCmdMode"/> class.
        /// </summary>
        /// <param name="sqlConn">The SQL conn.</param>
        public ExecuteSQLCmdMode(SqlConnection sqlConn)
        {
            // Check for legal values
            if (sqlConn == null)
                throw new Exception("sqlConn cannot be null");

            // Set connection variable from supplied SQLConnection
            _sqlConn = sqlConn;

            // Load up the script variables
            _variables = new Dictionary<string, string>();
            _variables.Add("SQLCMDUSER", "");
            _variables.Add("SQLCMDPASSWORD", "");
            _variables.Add("SQLCMDSERVER", sqlConn.DataSource);
            _variables.Add("SQLCMDWORKSTATION", sqlConn.WorkstationId);
            _variables.Add("SQLCMDDBNAME", sqlConn.Database);
            _variables.Add("SQLCMDLOGINTIMEOUT", sqlConn.ConnectionTimeout.ToString());
            _variables.Add("SQLCMDSTATTIMEOUT", "0");
            _variables.Add("SQLCMDHEADERS", "0");
            _variables.Add("SQLCMDCOLSEP", "");
            _variables.Add("SQLCMDCOLWIDTH", "0");
            _variables.Add("SQLCMDPACKETSIZE", "4096");
            _variables.Add("SQLCMDERRORLEVEL", "0");
            _variables.Add("SQLCMDMAXVARTYPEWIDTH", "256");
            _variables.Add("SQLCMDMAXFIXEDTYPEWIDTH", "0");
            _variables.Add("SQLCMDEDITOR", "edit.com");
            _variables.Add("SQLCMDINI", "");
            _variables.Add("SQLCMDREAL", "0");

            // Setup pre-locked variables
            _lockedVariables = new List<string>();
            _lockedVariables.Add("SQLCMDREAL");

            // Setup the list of commands to be ignored
            _ignoredCommands = new List<string>();
            _ignoredCommands.Add(":ED");
            _ignoredCommands.Add(":ERROR");
            _ignoredCommands.Add(":!!");
            _ignoredCommands.Add(":PERFTRACE");
            _ignoredCommands.Add(":QUIT");
            _ignoredCommands.Add(":EXIT");
            _ignoredCommands.Add(":HELP");
            _ignoredCommands.Add(":XML");
            _ignoredCommands.Add(":R");
            _ignoredCommands.Add(":SERVERLIST");
            _ignoredCommands.Add(":LISTVAR");

            // Some other misc values
            _errorMode = errorMode.errExit;
            _allowVariableOverwrites = false;

        }

        /// <summary>
        /// Gets or sets a value indicating whether to allow variable overwrites.
        /// If True then even though a variable is specified externally it may
        /// be overwritten by :SetVar in the script. If False then the reverse
        /// variables specified externally superscede :setvar
        /// Default = false
        /// </summary>
        /// <value>true if allow variable overwrites; otherwise, false.</value>
        public bool AllowVariableOverwrites
        {
            get { return _allowVariableOverwrites; }
            set { _allowVariableOverwrites = value; }
        }

        /// <summary>
        /// Sets a variable in advance of script execution.
        /// </summary>
        /// <param name="variableName">Name of the variable.</param>
        /// <param name="variableValue">The variable value.</param>
        public void SetVariable(string variableName, string variableValue)
        {
            variableName = variableName.Trim().ToUpper();
            if (variableName.Length == 0  variableName.Contains(" "))
                throw new Exception("Variable name cannot be blank or contain spaces");

            // See if we already have this variable
            if (_variables.ContainsKey(variableName))
                _variables[variableName] = variableValue;
            else
            {
                _variables.Add(variableName, variableValue);

                if (!_allowVariableOverwrites)
                    _lockedVariables.Add(variableName);

            }

        }

        /// <summary>
        /// Executes the specified SQL script.
        /// </summary>
        /// <param name="scriptToExecute">The SQL script.</param>
        public List<Exception> Execute(string scriptToExecute)
        {
            List<Exception> exceptions = new List<Exception>();
            StringBuilder sqlCmd = new StringBuilder();
            int begPos;
            int endPos;
            string temp;
            string[] SQLScript;
            SqlTransaction trx = null;
          
            _sqlConn.Open();
            trx = _sqlConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            try
            {
                SQLScript = (scriptToExecute.Replace(Environment.NewLine, "\n") + "\nGO\n").Split('\n');

                // Loop each line in the script
                for (int i = 0; i < SQLScript.GetUpperBound(0); i++)
                {
                    // Prepare a specially modified version of the line for checking for commands
                    string ucaseLine = SQLScript[i].Replace("\t", " ").Trim().ToUpper() + " ";

                    // See if it's one of the commands to be ignored
                    if (!_ignoredCommands.Contains((ucaseLine).Split(' ')[0]))
                    {
                        // See if we have a GO line (everything after GO on the line is ignored)
                        if (ucaseLine.StartsWith("GO "))
                        {
                            try
                            {
                                if (sqlCmd.Length > 0)
                                {
                                    // Attempt the SQL command
                                    SqlCommand sqlComm = new SqlCommand(sqlCmd.ToString(), _sqlConn, trx);
                                    sqlComm.ExecuteNonQuery();
                                }
                            }
                            catch (Exception ex)
                            {
                                if (_errorMode != errorMode.errIgnore)
                                    throw new Exception("Error executing " + sqlCmd.ToString(), ex);
                                else
                                    exceptions.Add(new Exception("Error executing " + sqlCmd.ToString(), ex));
                            }

                            // Reset the SQL Command
                            sqlCmd = new StringBuilder();

                        }

                        // Handle :SetVar MyVar "MyValue"
                        else if (ucaseLine.StartsWith(":SETVAR "))
                        {
                            temp = SQLScript[i].Trim().Substring(8, SQLScript[i].Trim().Length - 8);
                            begPos = temp.IndexOf(" ");

                            string varName = temp.Substring(0, begPos).Trim().ToUpper();
                            string varValue = temp.Substring(begPos + 1, temp.Length - begPos - 1).Trim();
                            if (varValue.StartsWith("\"") && varValue.EndsWith("\""))
                                varValue = varValue.Substring(1, varValue.Length - 2);
                            else
                                throw new Exception(string.Format("Improperly formatted :SetVar on line {0}.", i));

                            if (_variables.ContainsKey(varName))
                            {
                                if (!_lockedVariables.Contains(varName))
                                    _variables[varName] = varValue;
                            }
                            else
                                _variables.Add(varName, varValue);

                        }

                        // Handle :on error
                        else if (ucaseLine.StartsWith(":ON ERROR "))
                        {
                            temp = ucaseLine.Substring(10, ucaseLine.Length - 10).Trim();
                            if (temp == "EXIT")
                                _errorMode = errorMode.errExit;
                            else if (temp == "RESUME"  temp == "IGNORE")
                                _errorMode = errorMode.errIgnore;
                            else
                                throw new Exception(string.Format("Unknown On Error mode '{0}' on line {1}", temp, i));
                        }

                        // Regular SQL Line to have variables replaced on then added to SQLCmd for execution
                        else
                        {
                            temp = SQLScript[i];

                            // Quick check to see if there's any possibility of variables in the line of SQL
                            if (temp.Length > 4 && temp.Contains("$("))
                            {
                                // Loop each variable to check the line for
                                foreach (KeyValuePair<string, string> keyPair in _variables)
                                {
                                    string SearchFor = string.Format("$({0})", keyPair.Key);
                                    begPos = temp.ToUpper().IndexOf(SearchFor);
                                    while (begPos >= 0)
                                    {
                                        // Make the variable substitution
                                        endPos = begPos + SearchFor.Length;
                                        temp = temp.Substring(0, begPos) + keyPair.Value + temp.Substring(endPos, temp.Length - endPos);

                                        // Calculate a new begPos
                                        begPos = temp.ToUpper().IndexOf(string.Format(SearchFor));
                                    }
                                }
                            }

                            sqlCmd.AppendLine(temp);
                        }

                    } // If !IgnoreCommand
                } // for ...

                // All is well so commit the transaction
                trx.Commit();

            } // Try
            catch (Exception ex)
            {
                // Rollback transaction
                if (trx != null)
                    trx.Rollback();

                throw ex;


            }

            return exceptions;

        } // Execute
    } // Class

    /// <summary>
    /// Legal values for the error mode
    /// Error mode controls what happens when a SQL Error occurs
    /// </summary>
    public enum errorMode
    {
        errExit,
        errIgnore
    }
}

Sharepoint Versions

Just a note to anyone looking for SharePoint version #'s

Penny Coventry has an awesome article in her Blog covering most sharepoint version #'s. Really handy!

Penny Coventry
http://mindsharpblogs.com/penny/articles/481.aspx

List of Windows services for a couple versions of SharePoint

I needed this for a current project (and I still need WSS 2.0, SPS 2003, SPS 2001, WSS 1.0) and can't find the info anywhere so had to install SharePoint just to get it. Not sure if anyone will find it useful.

Services installed for WSS 3.0

SPAdmin
SPSearch
SPTimerV3
SPTrace
SPWriter

Services installed for MOSS 2007

OSearch
SPAdmin
SPSearch
SPTimerV3
SPTrace
SPWriter
ssosrv (not dedicated to SharePoint)

Cool iPhoto trick

 

This keystroke trick will unlock a few useful extra editting functions in iPhoto.

 

http://www.macosxhints.com/article.php?story=20060504123458727

 

 

Use this super-secret method to access some super-secret advanced editing modes in iPhoto6:

1. Enter edit mode for any image.
2. Select the Retouch or Red Eye tool.
3. Press Caps Lock, Control, and 9 at the same time, then release them.
4. Press Tab. If you activated the Red Eye tool, the small cross hairs will be replaced with a large oval. If you were using the Retouch tool, your cursor is now a small dotted circle with "0.50 { }" below it.
5. For either tool, press the "[" and "]" keys to alter the size of the area the brush covers.