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 (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() calls 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"

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.

Any help here would be most appreciated. Has anyone else encountered
this error?

Thanks,

Jon

----
http://csharponphone.blogspot.com