Sunday, July 06, 2008

SqlCeEngine.Upgrade() causes SqlCeException "The operation completed successfully."

I am building a simple desktop-based command-line analysis tool for a set of .SDF files that were collected on various mobile devices for a research project. Unfortunately, I am having issues connecting to these .SDF files from my tool (each of which are stored locally on my desktop hard drive). The problem seems to be that the version of SQL Compact Edition used on the mobile devices is different from the one I have installed on my desktop.

First, when connecting to one of the .SDF files from my desktop analysis tool, I receive the exception:

System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException was unhandled
Message="The database file has been created by an earlier version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade() method."
Source="SQL Server Compact ADO.NET Data Provider"


I now catch this exception and call the recommended SqlCeEngine.Upgrade() method, so my code looks like this:

SqlCeConnection sqlConnection = GetConnection(pathToDatabase);

try
{
sqlConnection.Open();
}
catch (SqlCeInvalidDatabaseFormatException e)
{
Console.WriteLine("The database " + pathToDatabase + " appears to be an old version, running Upgrade()");
Stopwatch sw = Stopwatch.StartNew();
SqlCeEngine sqlCeEngine = new SqlCeEngine(GetConnectionString(pathToDatabase));
string newPathToDatabase = GetUpgradedDatabasePath(pathToDatabase);
string newConnectionString = GetUpgradedConnectionString(pathToDatabase, newPathToDatabase);
sqlCeEngine.Upgrade(newConnectionString);
sw.Stop();
Console.WriteLine("Upgraded the database in " + sw.ElapsedMilliseconds / 1000.0f + " secs");
sqlConnection = GetConnection(newPathToDatabase);
sqlConnection.Open();
}


The SqlCeEngine.Upgrade() call takes about one minute, afterwards it throws a very confusing exception:

System.Data.SqlServerCe.SqlCeException was unhandled
Message="The operation completed successfully."
Source="SQL Server Compact ADO.NET Data Provider"


Figure 1: One of the most confusing exception messages I've ever received.

At first I thought I should just catch this exception and carry on normally (as the exception says that the operation did indeed complete successfully); however, after some experimentation I noticed that the new, upgraded .SDF file built by the Upgrade() command is automatically deleted right before this exception is thrown so there is no upgraded .SDF file to connect to. Thus, it appears that the SqlCeException is valid but that the message is just wrong. I have attempted to use both the SqlCeEngine.Upgrade() call (which upgrades the database in place) and the SqlCeEngine.Upgrade(newConnectionString) call (which makes a new database). Both fail with the same exception.

Figure 2: Note how the upgraded database appears in the filesystem momentarily before being automatically deleted just before the Upgrade() method throws an exception.

Any help here would be most appreciated. Has anyone else encountered this error? Note, I also posted this to the SQL CE newsgroup here.

Update July 6th, 8:04PM: One workaround for this problem is to use the old v3.1 libraries in your desktop program. Thus, rather than use the .NET tab in the "Add Reference" dialog in Visual Studio, you would select "Browse" and then browse to the 3.1 libraries. For me, this is "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1" Thankfully, Microsoft was smart enough to allow both sets of dlls on a machine (e.g., their 3.5 installation did not overwrite the 3.1 installation) as I also have a folder entitled "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5" with the v3.5 libraries.

2 comments:

Anonymous said...

hi, can you provide me sample sdf file generated by you? I'm stuck in generating a valid sdf file for MyExperience project. Mail me to askshameer@yahoo.com I appreciate your response.... tnx

The Mad Ape said...

As far as I know this will not work with 64-bit operating systems.

Microsoft has demonstrated their incompetence yet again.

There should have been built in integration of 3.1 sdf's