Friday, June 18, 2010

Accesing an .mdb database file fails after SQL Server upgrade

I have a feeling this is a pretty classic problem, and there's probably a variety of related problems out there - anyways, this caused me some headache today:

Scenario: I am developing an application that uses a simple .mdf database file (SQL Server Compact) to store and retreive data. The app runs well, even when taking it to other computers where SQL Server Express (SSE) is installed as a dependency application for my app. Today I installed a new version of SSE (2008 R2) on my own computer that I use to develop. Because of some clutter of old installations (2005 and 2008), I first removed the existing SSE completely, then installed the new one.

Problem: When trying to run my app after having installed the new SSE, it crashed with the following message:

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance

At first I did a compare of the parameters used to start the SQL Server service and the SQL VSS Writer with the same services on other computers where it worked. They were identical!

Solution: This posting on social.msdn.microsoft.com gave me the solution. Since I am running Vista, I renamed the following directory (instead of deleting it, to make sure I had a safe return in case it made things worse):

C:\Users\[username]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

I restarted my application, and voilla! I was back on track!

Reason: According to Scott J. Baldwin on the above mentioned posting, referring to Reen's suggestion B, "this error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works" - Thanks guys!

Friday, June 11, 2010

Creating an installer for your software that checks for and installs .NET and SQL dependencies

I developed a small software package for a client utilizing a SQL Server 2008 Express database and .NET 3.5 technology. The client wanted a simple installation package to distribute the software to several computers. Hence, the package should check for dependencies, like whether or not the .NET 3.5 framework and SQL Server 2008 Express was installed.

My eyes quickly fell upon the excellent Inno Setup freeware package. Together with this adaption of Inno found on The Code Project website, to include and/or download the needed dependency packages, it turned out to be the perfect tool for the job.

I had to develop a new script to be able to include the SQL Server 2008 R2 Express, SQL Compact Edition 3.5 for Desktops and MS Installer 4.5 in my package, but it was a breeze with all the good work already done. Check out my posting over at The Code Project to see these adaptions.

Thursday, June 3, 2010

Change of hard drive - cloning/restoring with BartPE/DriveImageXML

The following is not particularly ground breaking, just a note to self on how to do a HDD (hard disk drive) backup and restore as hassle-free as possible. My choice of tool was a BartPE CD that I built including the disk cloning tool DriveImageXML (or DIX) as a plugin.

Making the clone file was the easy part - DIX simply lets you make a clone file to a USB drive from within a running Windows OS. Getting the new HDD to boot after restoring the clone file to it was a little trickier. This is what I ended up doing
  1. Install DIX on the old drive running Windows, then creating a backup (clone) of the whole partition to a file on a USB hard drive. Yes, you can actually do that while Windows is running off of the same disk!
  2. Removed the old HDD and installed the new HDD in the computer (since it was a laptop with only one HDD bay, I did not have the option of leaving the old one in until the data was transferred).
  3. Partitioning was a little tricky. Unless you find a partitioning plugin for BartPE, you will have to do it from some other bootable media. I first tried an old Win98 command-line bootable CD, which only formatted around 130Gb (my drive was 160). I ended up booting into a Knoppix Linux boot CD and create a standard, active (=bootable) partition by running its fdisk from a command shell after issuing a su to gain administrator access. The partition was marked as a Linux type partition, which does not matter since DIX will overwrite it anyway, it just does not know how to create the partition. I might also have created the partition by booting into a Windows installation CD, creating the partition and then abort further installation.
  4. Now booting into the BartPE CD, I ran DIX again on the new disk, restoring the image from my USB disk. To get Bart to see the USB disk, make sure that it is on and connected already at boot.
  5. Booting from the new disk was the trickiest. When attempting to boot from the new disk, only a blank black page with a blinking cursor appeared. I had to boot into the Windows installation disk and choose Recovery console. From there, I ran fixboot, fixmbr and bootcfg /rebuild. Seems like my hard drive lacked both a working master boot record (MBR) and because my old disk had a utility partition that I had not bothered taking with me, the partition entry in boot.ini was also wrong.
Finally, my new disk booted with all the old data and programs as it was before.