Database corruption from simple query

Hi- I have questions about mobile database limits. The application is entirely C# / Compact Framework 2.0, using VS2005. Devices we support are all running WM5. We're trying to incorporate what seems to be a large database that the application needs to talk to offline from any network, so it needs to be stored & hosted within the database and hence we started working with the various generations of what is now SQL Server Compact Edition.

In order to profile the responsiveness I worked up a test database with the general collection of fields we are interested in. The main table has 80,000 rows with one primary key and one of the numeric fields indexed. There is a 'nickname' table which contains about 150,000 items with just a string for a nickname and the key that nickname goes with. With all the rows and an alpha index for the nickname field the database is about 42 mb. Which is pushing the amount of storage on the handheld, but of our two models one is fine and one still has about 30 mb free with the DB on the handheld.

My problem seems to be that one kind of device can work with this database and one cannot. The device I was using the prototype the application is a HP iPAC hx2490b. On that device more than once I have corrupted the database, but not yet as a reproducible process.

My other device is a Dell Axim X51. On that device I seem to corrupt the database with very simple actions. Just now I tried a query like (From the Query Analyzer 3.0)

Code Snippet

Select * from ReagentNicknamesTable where alt_names like 'alpha%'

Which should hit 2099 of the 150,000 records. I got about 400 back and then the table was corrupted. On this device I cannot recover the database as there isn't enough disk space for two copies of the DB. So corruption in the field would be a showstopper. But getting corruption from a simple query like this means I really can't use SSCE.

This problem with the dell showed up when I was using the original device sql that came with VS2005. (SQL Server Mobile?) The cabs & dll's were dated from 2005 and had a build number of 3.0.5206.0. I have since updated to the current version of SQL Server Compact Edition and the dlls have build number 3.0.5300.0. The IDE is set to Compact Framwork 2.0, sp1: cgacutil reports CF 2.0.6129.0.

So help! Have I hit some listed or unlisted limits to SSCE on the Compact Framework? thanks.

So on the Dell handheld, I did a hard reset, redeployed everything and copied a fresh copy of the DB onto the handheld.

Running the above query from the QA yielded the same error. But this time I was able to copy the text before I moved on.

Here is the result:

400 row(s) affected
FAILED: Select * from ReagentNicknamesTable where alt_names like 'alpha%'
Error: 80004005 Unspecified error
Error: 0x80004005 E_FAIL
Native Error: (25017)
Description: The database file may be corrupted. Run the repair utility to check the database file. [,,,Database name,,]
Interface defining error: IID_IRowset
Param. 0: 0
Param. 1: 0
Param. 2: 0
Param. 3: My Documents\ChemistryLabacus\data\ReagentDatabase.sdf
Param. 4:
Param. 5:

Running this exact query on the HP iPAC yields the 2099 rows that match this query.
|||

Try to move the SDF file and the "temp path" to a storage card.

So your connection string would look something like:

"Data Source=\Storage Card\database.sdf;temp path=\Storage Card"

|||Thanks for the reply. But, simply fixing the database is not really the issue. I could do it on a different handheld (the HP) that just ships with more memory. Or, I imagine, on a desktop with the desktop version of SSCE. The real question is why I get corruption in the first place. And why on the Dell but not the HP, when as far as I can tell they are running the same OS & DB engine.

Since my post yesterday we have culled the main database down to one that is is smaller (45k rows in the main table & 77k rows in the nickname index table.) This database is 20 mb with indexes. So far it has not shown corruption on the Dell.

The main reason that I'm hung up on the corruption issue (other than that it is annoying) is that we have a body of users who have these Dell handhelds and I'm supposed to build a release with this database to send to them. I would like some confirmation of good practices to avoid the corruption in the first place. Are there articles or forums who discuss the capacities and limitations of SSCE under WM5?
|||

Check your program memory, if it's too low it could crash. Happened to me many times, and it gives an empty exception message.

|||Thanks. I will look into that.