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

Source: Microsoft® SQL ServerT 2000 Windows® CE Edition 
Number: 80004005
NativeError: 25035
Description: Unspecified Error
				

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.