Re: Concurrent database access in SQL 2005 Mobile

This doesn't sound quite right to me, so let me ask you some questions and see if we can figure this out.

1. What version of SQL CE are you using? I'm assuming 3.1. If so, there is also 3.5 that is available, and 3.5 sp1 which is in beta and due to be released very soon.
2. Are you accessing the database from an app written in C++? The OleDb implementation for SQL CE is not a complete implementation and you'd get more functionality using the managed code provider if you are writing in a managed code language.
3. Are you accessing the database from multiple threads in any of the apps?
4. Are all the apps accessing the database on the same physical machine?
5. How is the main app "looking" to see changes? Is it issuing a new query for example? It wouldn't seem to me that you'd need a new connection to issue a new query, but I don't use OleDb against SQL CE data so I might have missed something.

Thanks,

--

Ginny Caughey
Device Application Development MVP


"sardo007" <sardo007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:015F6EF9-93C2-425B-9759-2B29214E477A@xxxxxxxxxxxxxxxx
Hello all... I'm having a very annoying issue that's about to make me crazy.

I have a database application developed with VS2005 and OLEDB access to a
SQL 2005 Mobile database. While the application is running the same database
can be accessed by another notifying application that shoud add, modify or
delete rows in some tables. The problem is as follows:

1- If the main application creates, uses and then destroys the OLEDB
connections, everything works just fine: changes made by the notifying app.
are seen by the application. This just works too slowly for the customer in
some devices.

2- One workaroud for the previous problem I found in these forums is to
create a "dummy" connection and keep it open. That way the database file is
maintained in memory and every subsequent connection is created and opened
faster. This works fine except that any changes made by the notifying
application is not seen by the main application until the user exits and then
enters again.

3- I have recently discovered another problem: if the notifying program
deletes one row in a table, and the user tries to modify the contents of that
row (since he stills sees it, because the changes are invisible to him) the
database gets corrupted and it can't be accessed anymore from neither
application. Reads to the "nonexistent" data are OK, but they just should not
be there.

I've tried to change everything from transaction isolation method to rowset
properties regarding to the data visibility and the like... nothing works
except the "true" reset of the database file (closing all connections and
opening them again when you need them)

Is there a known issue, patch or anything I have to do explicitly to solve
this problem?

Any ideas will be welcome.

Thanx


.

Relevant Pages

  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: User not associated with trusted SQL Server connection
    ... > using the osql utility. ... > associated with a trusted SQL Server connection. ... > database or is it just for that instance. ...
    (microsoft.public.sqlserver.security)
  • RE: Manipulating MS Access records with excel VBA + ADO
    ... While I cannot see how a working example of a connection to a database is ... Select the control button view dataor edit query. ... Look for SQL button to get the SQL ...
    (microsoft.public.excel.programming)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... has been done with parameterised SQL, ... bit of code into my test which closes and reopens the database ... opening and closing the DB connection is not a good idea. ... // Display all error messages ...
    (microsoft.public.sqlserver.ce)