SQL Express is a great idea; I use it when developing applications so I can isolate everything from my "grown up" version of SQL until it's ready to go live. Admittedly it has problems; when a program mounts a user instance of a database it connects as dbo, so if you've missed setting permissions on your tables and stored procedures you won't find out until you migrate, but that pain is minimal compared to how handy it is.

This week I've been trying to upgrade me SQL Express instance to SP2; as you can guess from the fact I'm blogging about it it's not been a smooth process. I'd previously updated my grown up, developer edition of SQL on my laptop to SP2, but that upgrade process doesn't touch any SQL Express instance. In order to upgade SQL Express you need to download the full Express installers and run them. And it fails.

After running through that slow startup procedure and pointing the Express MSI to my SQLEXPRESS instance it starts to go; then dies, complaining with

The error code is 2259. Database:  Table(s) Update failed

The installation log file points to a problem finding file paths;

MSI (s) (B4!EC) [18:23:49:453]: Note: 1: 2259 2:  3:  4: 
<Func Name='GetFileTargetPath'>
        Error Code: 0x8007065b (1627)
Windows Error Text: Function failed during execution.
  Source File Name: darlib\viewinstaller.cpp
Compiler Timestamp: Wed Jun 14 16:19:22 2006
     Function Name: sqls::ViewInstaller::modify
Source Line Number: 137
---- Context -----------------------------------------------
Setting status of unmanaged components and removing unmanaged resources
Failed to modify installer view
1: 2259 2:  3:  4: 
Error Code: 1627
MSI (s) (B4!EC) [18:23:54:890]: Product: Microsoft SQL Server 2005 -- Error 2259. The installer has encountered an unexpected error. The error code is 2259. Database:  Table(s) Update failed

Isn't that helpful? As SQL Express is isolated from everything else I though I could just uninstall it then reinstall with the new version. Except that won't work either; if I try to uninstall the MSI complains it can't find sqlrun_sql.msi. So I thought I'd download the Express SP1 installer, run it and choose the uninstall option. A download later and I find that the install process is just that, an install process and you can't use it to uninstall; uninstalls must be done from Add or Remove programs, and that doesn't work.

So I reran the SQL Express SP2 installer, and cancelled it, after taking a copy of the install directory it creates so I have the MSIs extracted. Pointing the uninstaller to those doesn't work either, apparently it's not a valid installation package. I tried the same process with the SP1 installer and got the same result.

So I pulled down the original SQL Express package, ran the installer, copied the extracted MSIs and cancelled at the EULA screen, then tried the uninstall process again. This time it hung when attempting to configure the "SQL Setup Support Files". After waiting 30 minutes I cancelled that and discovered to my surprise that the SQL Express service had gone.

My jubilance at having thought I had gotten rid of it was short lived. Now attempting to install the full SQL Express SP2 package hangs at the same place, configuring the SQL Setup Support files. The huge install log shows no errors, but is full of the repeating lines (making up a 95Mb installation log)

1: sxsdelca 2: traceop 3: 749 4: 0 
1: scavenge 2: {4B1FA220-DF45-47E0-A3B5-F2576C801489} 3: {51DF7ECC-09CA-F455-B01F-C8B3B9A1E18E} 4: {53F5C3EE-05ED-4830-994B-50B2F0D50FCE} 5: -1 6: 1607

What joy. Now I am stuck; I can't install SQL Express again and all my projects that use it are broken. Including one I am supposed to be using in a presentation this month. Thanks MS. Thanks a bundle.