Select the product you need help with

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

Article ID: 271931 - View products that this article applies to.

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

Back to the top | Give Feedback

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.

Back to the top | Give Feedback

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

274112

(http://support.microsoft.com/kb/274112/EN-US/ )

INFO: Performance Tips & Efficient Ways to Handle Memory for SQL CE

Back to the top | Give Feedback

Properties

Article ID: 271931 - Last Review: February 22, 2007 - Revision: 1.1

APPLIES TO

Microsoft SQL Server 2000 Windows CE Edition

kbinfo KB271931

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback