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

[jonfroehlich]

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

[Ginny Caughey MVP]

Jon,

I'd agree from your description that probably the exception is right and the
message is wrong. Is it possible that some of the SDF files were created
with a newer version of SQL Compact? Which version are you using?

Here's one way to find out what version of the files you're working with:

http://blog.opennetcf.com/ctacke/2008/04/12/DetermingingASQLCEDatabaseVersion.aspx

You may also want to try out this 3rd party product, which works with a
variety of different versions of SDF files:

http://www.primeworks-mobile.com/Products/DataPortConsole.html

Ginny Caughey
Device Application Development MVP

"jonfroehlich" <jonfro...@gmail.com> wrote in message
news:50eb7383-83d0-481d...@79g2000hsk.googlegroups.com...

[jonfroehlich]

Hi Ginny,

I don't know what this group would do without you :) I believe the SQL
Compact Edition version used on the mobile devices was 3.1, however,
my desktop is using 3.5. The work around I am now using is to
configure my desktop app to use the 3.1 SQL CE libraries as well. I
have experienced no problems since making the switch; however, I still
think that SqlCeException message I received should probably be
investigated by an MS dev.

The full write-up and workaround is posted here:

http://csharponphone.blogspot.com/2008/07/sqlceengineupgrade-causes.html

Jon

On Jul 6, 9:26 pm, "Ginny Caughey MVP"

<ginny.caughey.onl...@wasteworks.com> wrote:
> Jon,
>
> I'd agree from your description that probably the exception is right and the
> message is wrong. Is it possible that some of the SDF files were created
> with a newer version of SQL Compact? Which version are you using?
>
> Here's one way to find out what version of the files you're working with:
>

> http://blog.opennetcf.com/ctacke/2008/04/12/DetermingingASQLCEDatabas...

>
> You may also want to try out this 3rd party product, which works with a
> variety of different versions of SDF files:
>
> http://www.primeworks-mobile.com/Products/DataPortConsole.html
>
> Ginny Caughey
> Device Application Development MVP
>

> "jonfroehlich" <jonfroehl...@gmail.com> wrote in message

[Ginny Caughey MVP]

Thanks for the follow up, Jon. I'll make sure the team is aware of the
message if that's still what happens with 3.5 sp1 beta - I haven't tried it
yet.

Ginny

"jonfroehlich" <jonfro...@gmail.com> wrote in message

news:d0823954-1755-4c17...@d1g2000hsg.googlegroups.com...

[Ginny Caughey MVP]

Jon,

This code behaves as expected with a SQL Compact 3.1 database for me with
whatever 3.5 sp1 beta I have on my machine:

string pathToDatabase = @"Data source=c:\tickets.sdf";
SqlCeConnection sqlConnection = new SqlCeConnection(pathToDatabase);
try
{
sqlConnection.Open();
}
catch (SqlCeInvalidDatabaseFormatException eSql)
{
SqlCeEngine sqlCeEngine = new SqlCeEngine(pathToDatabase);
string newPathToDatabase = @"Data source=c:\tickets35.sdf";
sqlCeEngine.Upgrade(newPathToDatabase);
sqlConnection = new SqlCeConnection(newPathToDatabase);
sqlConnection.Open();
sqlConnection.Close();
}

Ginny

"jonfroehlich" <jonfro...@gmail.com> wrote in message

news:d0823954-1755-4c17...@d1g2000hsg.googlegroups.com...

[Laxmi Narsimha Rao ORUGANTI [MSFT]]

Hey Ginny and Jon,

I am a developer in SQL Server Compact Team and we definitely want to look
at this issue in much deeper. Though I can not guarrenty I can get a fix
for you in v3.5 SP1 RTM (It is internally went to RTM already); it would
still be important for us to find the root cause. Once we confirm that
there is indeed a bug in the code, you can get the fix by going thru normal
support channel if required (free of cost).

Can you please contact us thru the blogs:
http://blogs.msdn.com/sqlservercompact/contact.aspx OR
http://blogs.msdn.com/laxmi/contact.aspx

Thanks,
Laxmi [Disclaimer: All postings are as-is and confer no rights]

"Ginny Caughey MVP" <ginny.caug...@wasteworks.com> wrote in message
news:216BDF2F-8E5D-4602...@microsoft.com...

[Ginny Caughey MVP]

Thanks, Laxmi. Good to hear! Do you need a repro case? I'm not seeing with
with 3.5 sp1 beta.

Ginny

"Laxmi Narsimha Rao ORUGANTI [MSFT]" <laxm...@online.microsoft.com> wrote
in message news:eMaYFG34...@TK2MSFTNGP02.phx.gbl...

[Laxmi Narsimha Rao ORUGANTI [MSFT]]

Jon seems to have reproduced it with a particular database. So it is highly
likely that you are not able to reproduce the same issue with your set of
databases. I want to get hold of that particular database to really see
which combination of on-disk pages causes this issue. If you happen to have
got the copy please post it.

Thanks,
Laxmi [Disclaimer: All postings are as-is and confer no rights]

"Ginny Caughey MVP" <ginny.caug...@wasteworks.com> wrote in message

news:0FE4D7EF-19D6-47B9...@microsoft.com...