Re: Sql ce server process

Target platform is currently WM2003, and it is understood that future
platforms may have sql in ROM - which you are correct would make this
solution a moot point. We are unlikely to target another platform until WM7
is out, which takes the whole VM and process count limitation issues out of
the picture, but the devices we are deploying on today are expected to be
around for the next 5 years or so, so we have to consider ways to continue to
add functionality in this constrained environment.

As for the reliable connection - the engine, running on a particular device,
would only be interfaced by a process (or processes) running local on that
device - we would not in general use this to interact with the database
remotely, though this may end up being handy in certain support situations.
My plan is not to try to use sql in any more robust of a fassion than was
designed - only to extract that functionality into a separate process where
it has more freedom to roam in it's own VM slot. I do not expect to put a
load on the database that is any more heavy than the load that exists today,
when the application uses the sqlmobile API in-process (actually probably
less load, since the new process should have some free VM to cache query
plans).

We are definitely focused on the occasionally connected end of things - our
devices are in vehicles roaming the country on GPRS... This is one of the
reasons we exported all of the replication logic into it's own process in
order to handle a lot of the retry logic without bogging down the user's
interface.

"Ginny Caughey [MVP]" wrote:

Brandon,

It's not necessarily a bad idea, but I'm not sure if it's a great one
either, and it might be more complicated to implement than you expect. The
main problem I see with that approach is that it requires you to have a
reliable connection all the time or you're completely out of business. I
don't know what type of hardware you're targeting, but SQL Compact is in ROM
beginning with Windows Mobile 6, so the other 'problem' might be that newer
devices solve this problem for you, or at least this solution might not
actually solve your problem And a third issue might be serializing access
to data if more than one device might be using this service. Making that
robust could be more trouble than it's worth, and making it performant under
load might be impractical.

As for why somebody hasn't done this already - Microsoft has been focusing
more on the occasionally connected scenario with Sync Services for ADO.Net.
The smart device piece of that isn't available yet, but Sync Framework is
certainly the direction they're moving in. Steve Lasker even has a blog post
describing a "stored procedure" kind of approach to SQL Compact:
http://blogs.msdn.com/stevelasker/archive/2008/02/11/stored-procedures-and-sql-server-compact-the-great-debate.aspx
Definitely worth a read since it addresses working with queries in a uniform
way, although I don't think it addresses the specific issue you're trying to
solve.

--
Ginny Caughey
Device Application Development MVP

www.wasteworks.com
Scalehouse and Billing Software for Waste Management



"Brandon" <Brandon@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:09E3294C-67CD-4454-8F15-3C69A9459806@xxxxxxxxxxxxxxxx
I posted this on the compact framework board, as the reasoning behind the
direction described here is compact framework - related, but I though
there
might be an expert over here on this board that might be more familiar
with
possible past attempts in this direction....

We are running out of VM in our main application, and among other things,
I
am considering how we might break functionalities across groups of native
dll's that have to load for those functionalities. An obvious break line
involves sqlmobile (3.x). I threw together a quick proof of concept for
basically creating a database engine process that would perform all
sqlmobile
access, with a TCP interface for issuing commands and getting results
wrapped
in XML (we already do something similar in a process that performs all of
our
replication - conceivably, this process would grow up to be a sqlmobile
engine that handles both queries and replication). I know that there may
be
something we can do that will be more efficient than XML, but our result
sets
are generally small - probably the biggest one is 18 rows by about 30
columns. Hopefully an added benefit would be that we might even be able
to
put together an algorithm for caching a few query plans...we have been
unable
to do this for some time, due to VM constraints.

I can't imagine that this is as avenue that has not been explored, and am
just wondering if anyone has any comments on the approach - is this a bad
idea? It seems like if this were a viable option, that it would already
exist out there somewhere, probably even provided by Microsoft. Maybe
they
were just afraid people would try to use it in a more robust way than it
was
intended...?


.

Relevant Pages

  • Re: DBDATE / DBMONEY in SQL =?ISO-8859-1?Q?=E4ndern?= ??
    ... I'm not aware of a way to set the date format with an SQL command. ... possibility would be to load the data into an auxiliary table that is ... identical to the target table except that date columns are replaced by ...
    (comp.databases.informix)
  • Re: Sql ce server process
    ... We are certainly aware of the dll load issue, ... application that is using sqlmobile and is on the virge of disaster. ... running 2 processes at the same time, one that uses the SQL Mobile engine ... unmanaged SQL Compact DLLs is that you need to get them loaded early in the ...
    (microsoft.public.sqlserver.ce)
  • Re: load access table to sql
    ... I'm able to load the table now using very similar code. ... having is because of a column named "Group" in both the local and sql table. ... >> Dim rst As Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO.NET questions
    ... Sure, you can use XML to move data to and from SQL Server, but it's very ... >>> have a way to export rows from a few tables from one users computer to ... >>> email xml file to another user) ... >>> 5)Create SQLDataAdapter on target computer. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Specific LIKE statement
    ... But I need to load all records where field Name is one of the names in this string: "John is director, Jack is programmer, Jim is Analyst, James is manager". ... How to do that with SQL? ...
    (microsoft.public.dotnet.framework.aspnet)