SQL CE database upgrade

This blog will talk about SQL CE database upgrade.
Actually, SQL CE database upgrade is briefly discussed in BOL (Book On-Line). And this blog is just to fill the missing link.

Normally, You can create a brand new application to use .NET CF 2.0 & SQL CE 3.0. Then what's the big deal about the SQL CE database upgrade, you might ask. Well,What you actually face in real-life situation is you are .NET CF 1.0 & SQL CE 2.0 application developer. Due to performance issue (SqlCeResultSet Class) or new functionality that only available in SQL CE 3.0, you need to meet the new requirement yet need to maintain the records that already keep in existing SQL CE 2.0 database. You do not want to create a new SQL CE 3.0 database and import back all the records, ain't you? Then, this is where SQL CE database upgrade comes in.

How will one know the database file (*.sdf) is not workable in SQL CE 3.0 ? Simple, open it from MSSQL Management Studio (Sorry, you need full version to run it). If you see this message, clearly you need to upgrade your database.

Now I will show you how to upgrade it within the emulator environment, specifically for OS PPC 2003. It can be done in actual device in the similar way. First, copy all the cabinet files of SQL CE run-time components for SQL CE 2.0 and 3.0 into you emulator, using Windows CE Remote File Viewer. The default location of the SQL CE 2.0 run-time components is C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v2.0\[platform]\[processor] & the default location of the SQL CE 3.0 run-time components is C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\[platform]\[processor]. You also need a upgrade.exe. This files is located in the same folder with SQL CE 3.0 run-time components.

Then you need to download the Pocket Shell in Windows Mobile Developer Power Toys. It is the PPC version of you desktop command prompt. The default location is in C:\Program Files\Windows Mobile Developer Power Toys\PPC_Command_Shell\arm. Copy the console.dll to emulator's \WINDOWS directory and copy the shell to emulator root folder for less programming later.

So now you should have 5 cabinet files(sqlce20.dev.ppc.wce4.armv4.cab, sqlce20.ppc.wce4.armv4.cab, sqlce30.dev.ENU.ppc.wce4.armv4.cab, sqlce30.dev.ppc.wce4.armv4.cab, sqlce30.rep.ppc.wce4.armv4.cab), 3 executable files (upgrade.exe, cmd.exe & shell.exe from Power Toy) and one SQL CE 2.0 database (oldDB.sdf). Install all the 5 cabinet files & click on the cmd.exe to run it. You will be prompt to enter command.

Type something like: upgrade.exe /s oldDB.sdf /d newDB.sdf

viola! (You need to wait a while if your database is huge)

Try to open it with desktop MSSQL:

If you have missed any cabinet file during installation (eg. you did not install "sqlce30.rep.ppc.wce4.armv4.cab"), you will get this error message:

Try to export the upgrade.log file to analyze:

In this case, you will get:

Connecting to source databaseCreating destination databaseUnable to instantiate SQL CE 3.0 provider
HRESULT: 0x80040154Database upgrade failed