Microsoft KB Archive/271931

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

Article ID: 271931

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q271931



SUMMARY
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: 80004005 NativeError: 25035 Description: 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.

