Issue with SQL Server 2005 Mobile and SD Cards?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

adulador - 19 Sep 2007 21:12 GMT

Our application uses a database on an SD Card. We encounter strange
errors sometimes when we attempt to access it.

The errors are: 0x80004005, Description: Unspecified error; Minor = 0.

Once this occurs it never can recover. A soft reset sometimes solves
it, but not reliably.
Another observation is that after this error occurs, if you look at
the SD card in File Explorer, the file listing looks all messed up.
for example some folder and file names are,
========
Meta(====
nt="Robo.HEL
}boxboxol.wh

the file sizes are wacked out too. One said 1.58G and the card is only
512 MB.
The file system has gone corrupt or something.

Is there an issue with SQL Server 2005 Mobile and SD Cards?
Is there a setting i need to use when the DB is on an SD Card?

Environment Info:
DB: Sql Server 2005 Mobile Edition
in OLEDB C++.

Reply to this Message

Peter Hartlén - 20 Sep 2007 09:07 GMT

Hi!

I'd say there is no perfect solution using database on SD-card, and many
people would strongly advice against it.

There are several factors that help making life easier though if you for
some reason must have your db on an external disk.

1. Make sure the device has good drivers for the SD-card. We had several
problems with this in the beginning, but our device manufacturer eventually
solved this. Problems tend to arise when the device power-ups, the
application targetting the db is running, and the card drivers problably
doesn't initialize the card fast enough.

2. Make sure you use professional cards. Cards are so cheap nowadays anyway.
We've been using SanDisk Ultra II cards quite successfully. Older/Slower
cards has shown more tendency to malfunction. As your file system seems
corrupted from time to time I sugges trying this solution first.

Anyhow, if you are targeting WM5 or later I suggest putting the SD-card on
device memory instead. IIRC WM5 has some kind of data recovery handling, so
the database should be pretty safe (I'm not 100% sure on this).

/ Peter

> Our application uses a database on an SD Card. We encounter strange
> errors sometimes when we attempt to access it.
[quoted text clipped - 21 lines]
> DB: Sql Server 2005 Mobile Edition
> in OLEDB C++.

Reply to this Message

adulador - 21 Sep 2007 12:45 GMT

> Hi!
>
[quoted text clipped - 48 lines]
>
> - Show quoted text -

Peter, thank you for your response.

I have implemented a couple things as you have suggested.
1. We are experimenting with high quality cards. The ones reporting
the errors were, in fact, old and slow cards.
2. I detect a wake up of the device now and before accessing the DB
again after a wake up I tear down the old connection, check for the
existance of the SD card and db file and then create a new database
connection, if all is there.
3. When a failed query with an unknow error code is detected, I
attempt the query one more time after tearing down the old conneciton,
detecting the existance of the SD card and the db file and creating a
new connection, if all is well.

We are still waiting to see the results of the above changes, to see
if it cleared up the problem.

I am also starting to wonder if there are any settings in the DB that
could be tweaked to help an SD Card based database. One in particular
I am just starting to look at is the FLUSH INTERVAL. Do you have any
advice along these lines?

Reply to this Message

joe_w - 22 Sep 2007 00:43 GMT

> Hi!
>
[quoted text clipped - 48 lines]
>
> - Show quoted text -

We also are having major problems with SQL Server 2005 Mobile. Our
Databases are on the SD cards as well since they can get very large.
We use the San Disk Ultra II and have had good luck with stability of
the SD card but SQL will terminate my .NET application during any type
of Database action to the table like an Insert statement. There is no
way to catch the error - it just termiates the program. However, I
have found that if I open the database with Query Analyzer before I
run my program then everything works most of the time. We do open/
close/dispose of all SQL CE objects like connections, commands, etc
because that was necessary back in SQL CE 2.0 where it would corrupt
the tables if we didn't do this process.

The device is an HHP Dolphin 9500 unit running PPC 2003 SE - I've not
tested with any other O.S. yet.

Reply to this Message

Peter Hartlén - 24 Sep 2007 08:59 GMT

Hi Joe!

We are also using HHP Dolphin 9500 with PPC 2003 SE. What kernel are you
using? We are currently using 7.14 with SP1. Older kernels, especially
before 7.13 did show serious problems with the card drivers, we've been on
HHP quite alot about this. The current kernel is in my beliefe quite stable
and fast when it comes to SD-card performance.

Are you experiencing this after a power up?

One thing to note however is that I don't make any calls to the db before
I've successfully "initialized" the sd-card. Where initializing only means I
check for the existance of the SD-card (Storage Card) folder when a power on
event is detected. I'm using the Power Notification Code I think one of the
guys from opennetcf.org wrote.

Regards,

Peter

We also are having major problems with SQL Server 2005 Mobile. Our
Databases are on the SD cards as well since they can get very large.
We use the San Disk Ultra II and have had good luck with stability of
the SD card but SQL will terminate my .NET application during any type
of Database action to the table like an Insert statement. There is no
way to catch the error - it just termiates the program. However, I
have found that if I open the database with Query Analyzer before I
run my program then everything works most of the time. We do open/
close/dispose of all SQL CE objects like connections, commands, etc
because that was necessary back in SQL CE 2.0 where it would corrupt
the tables if we didn't do this process.

The device is an HHP Dolphin 9500 unit running PPC 2003 SE - I've not
tested with any other O.S. yet.

Reply to this Message

adulador - 25 Sep 2007 20:55 GMT

It sounds like a module loading problem. We had the same issue with
our WM 2003 devices.
Its the way DLL management is handled by WM2003. All apps must
allocate space for all dlls loaded by any application currenly
running.
There are two things you can do.
1. Reduce the number of DLLs you have in your application. Merge them
into the main EXE.
2. Load the database engine DLLs up front and hold onto their
references until the application shuts down. This explains why
openning your DB in Query Analyzer before hand helps. Query Analyzer
is preloading the DLLs for you.

Now, our code is native C++ so we accomplished this
with ::LoadLibrary() which you may have to InterOp to get access to.
Here is our code:
_hSQLCESE30_DLL = ::LoadLibrary(_T("\\Windows\\sqlcese30.dll"));
_hSQLCEQP30_DLL = ::LoadLibrary(_T("\\Windows\\sqlceqp30.dll"));
_hSQLCECOMPACT30_DLL = ::LoadLibrary(_T("\\Windows\
\sqlcecompact30.dll"));
_hSQLCEER30EN_DLL = ::LoadLibrary(_T("\\Windows\\sqlceer30en.dll"));
_hRSAENH_DLL = ::LoadLibrary(_T("\\Windows\\rsaenh.dll"));

rsaenh.dll is only needed if you encrypt the DB. Also, you will have
to load your specific .NET DLLS. I dont know their names, but you can
find them in the CAB files for the DB engine.

Hope this helps...

> > Hi!
>
[quoted text clipped - 65 lines]
>
> - Show quoted text -

Reply to this Message