Concurrent database access in SQL 2005 Mobile

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: How can I open an SQL database and be the only one who has access to it?
    ... you can determine how SS permits access to your server. ... SINGLE_USER allows one user at a time to connect to the database. ... controlled by the termination clause of the ALTER DATABASE statement. ... To allow multiple connections, the database must be changed to ...
    (microsoft.public.data.ado)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ... out of process memory errors. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... out of process memory errors. ... that Oracle is having issues with OS resources. ...
    (comp.databases.oracle.server)
  • Re: ORA-12518, 04030 errors after 9i-10G upgrade.
    ... New server has 6GB ram. ... All went ok until users started connecting in 100+ numbers, database is ... set up for dedicated server connections, this was never a problem in 9i. ... This did not solve problems so bounced database and decreased SGA to ...
    (comp.databases.oracle.server)
  • Re: Proper Database Connection Cleanup
    ... but I think two of them have a database query in them. ... Public Sub ConnectToDB2(byref XCmd, byref YRS, byRef Conn) ... Set ObjRS = ObjCmd.Execute ... from DB connections in my code or from something else? ...
    (microsoft.public.scripting.vbscript)