INFO: Using Multiple Recordsets for Performance with SQL Server CE Database

This article was previously published under Q271931
This article has been archived. It is offered "as is" and will no longer be updated.
SQL Server 2000 Windows CE Edition supports only a single connection to a database. If you open more than one connection to the same database, an additional Connection.Open call gives an explicit error 25035 (File sharing violation) SSCE_M_FILESHAREVIOLATION:
Source: Microsoft® SQL Server™ 2000 Windows® CE Edition Number: 80004005NativeError: 25035Description: Unspecified Error				
SQL CE Books Online displays the following if you search on 25035:
25035 File sharing violation SSCE_M_FILESHAREVIOLATION
More information
Two applications on a Windows CE device or Pocket PC cannot concurrently open a connection to the same SQL CE database.

This behavior is by design. Inside of an application, you can pass around the connection object and create multiple recordsets. This article describes how to use multiple recordsets for performance with a SQL CE application.

SQL CE does not allow you to open more than one active connection to the same database at the same time. The SQL CE engine can support a large number of simultaneously opened recordsets on one connection. However, there are other limiting factors, such as available memory, programming environment, and locking issues.Locking can become an issue if you are using multiple recordsets on the same object (generally with transactions).

Following are some helpful tips when you are working with multiple recordsets in a SQL CE application:
  • Minimize the number of open objects, including cursors and recordsets. Windows CE generally is a memory-constrained platform, and large databases can expose memory problems in your application if you are not careful.
  • When you open a recordset over a SQL statement (such as, SELECT * FROM TABLE), a large amount of work is required to compile, optimize, and execute a query. These operations are more expensive than opening a base table (by using the adCmdTableDirect flag).
Following are some examples of reasonable uses of cursors against SQL CE:
  • Have one cursor for screen painting and one for updates/inserts in the background, over the same data.

    NOTE: Depending on your cursor type, data values may not be refreshed to changes in the underlying data. See the SQL CE Books Online topic Cursor Types for more details.
  • Have a few tables open that you frequently use in an application.
  • Have one cursor opened on a query (SELECT * FROM table1 WHERE col1 > 2) and another opened on the base table to do edits and updates against that cursor.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
274112 INFO: Performance Tips & Efficient Ways to Handle Memory for SQL CE

Article ID: 271931 - Last Review: 11/01/2013 22:49:00 - Revision: 2.0

Microsoft SQL Server 2000 Windows CE Edition

  • kbnosurvey kbarchive kbinfo KB271931