Databases

MSDN Technical Articles

• Troubleshooting Microsoft SQL Server 2000 Windows CE Edition Connectivity Issues
  December 2001. Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) includes a robust error reporting mechanism that provides information about errors returned from a variety of sources. This paper describes how SQL Server CE errors are generated and how and where to find more information about these errors. The paper also describes some of the common connectivity errors that occur when running SQL Server CE and replication or Remote Data Access (RDA).
• SQL Server CE Relay
  February 2002. How to set up connectivity between Microsoft SQL Server 2000 Windows CE and Microsoft SQL Server by using SQL Server CE Relay.
• SQL Server CE: New Version Lets You Store and Update Data on Handheld Devices
  June 2001. Handheld device users need to be able to synchronize with a main data store when it's convenient and, preferably, when the back-end database server isn't busy. SQL Server 2000 Windows CE Edition allows you to build a traveling data store that can be displayed and run on a variety of devices. SQL Server CE supports a subset of the full SQL Server package, and can be used as a standalone server or in tandem with SQL Server and IIS. The architecture of SQL Server CE, along with data manipulation, synchronization, and connectivity issues, are discussed in this article. Topics such as making your data public, choosing the right type of replication, and handling errors are also covered.
• SQL Server 2000 Windows CE Edition and the .NET Compact Framework
  September 2002. Learn how to efficiently and easily interface embedded devices against Enterprise information systems with SQL Server CE and the .NET Compact Framework.
• Security Models and Scenarios for SQL Server 2000 Windows CE Edition 2.0
  November 2002. Learn how to set up Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) and Microsoft Internet Information Services (IIS) for connectivity security. Special emphasis is given to scenarios that are useful to application developers and testers, but not necessarily recommended in production environments. The paper covers requirements for multiple computers as well as for a single computer. After reading this document, you should be familiar with the details of setting up security and encryption on the server for use with SQL Server CE clients. This paper assumes that you are familiar with the two types of connectivity supported by SQL Server CE (replication and Remote Data Access).
• Security Models and Scenarios for SQL Server 2000 Windows CE Edition
  June 2001. This document describes how to set up Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) and Microsoft Internet Information Services (IIS) for connectivity security. The document discusses requirements for setting up a single computer as well as multiple computers. By the end of the document you should be familiar with the details of setting up security and encryption on the server for use with SQL Server CE clients. Before reading this paper, you should already be familiar with the two types of connectivity supported by SQL Server CE (replication and Remote Data Access).
• Converting an eMbedded Visual Basic 3.0 Application from SQL Server CE 1.x to SQL Server CE 2.0
  November 2002. Microsoft SQL Server 2000 Windows CE Edition 2.0 (SQL Server CE) uses the same file format and data access options as the previous version. This makes it extremely easy to convert an existing Microsoft eMbedded Visual Basic application to SQL Server CE 2.0.
• International Considerations in Applications Developed for SQL Server 2000 Windows CE Edition
  March 2001. This article discusses the limitations of developing applications for Windows CE devices using Microsoft SQL Server 2000 Windows CE Edition.
• ADOCE Record Set Errors
  There are often postings to the newsgroups relating to Microsoft ActiveXR Data Objects for Microsoft WindowsR CE (ADOCE) record set errors. Some of these questions are a result of overlooking the cursor functionality specified when the record set is defined.
• ADOCE for ADO Programmers
  Are you an ADO developer that needs to move your knowledge and even code from PC to Pocket PC data-enabled applications? I show you the similarities and differences between ADO and ADOCE as well as some code samples.
• ADOCE for ADO Programmers
  May 16, 2001. Leverage on your current ADO Visual Basic programming skills to do data access on your Pocket PC.
• Using ADOCE with Pure C++:
  Apr. 9, 2001. Make ADOCE your primary data storage technology while using Microsoft Visual C++.
• ADOCE Record Set Errors
  Feb. 13, 2001. Information on Microsoft ActiveXR Data Objects for Microsoft WindowsR CE (ADOCE) record set errors.
• Using ADOCE from a C++ Application
  Feb. 13, 2001. Using the ADO COM objects for database access allows C++ developers to take full advantage of SQL Server data in their applications.
• Not All Record Sets Are Created Equal:
  Jan. 16, 2001. An example of using the powerful ADOCE record set Supports method.
• Go Crazy! Try Some ADO
  Nov. 9, 2000. Written by Pocket PC users, for Pocket PC users.
• Speed up Your Applications with ADO for Windows CE (ADOCE)
  June 19, 2000. A Quick and Easy Way to Improve Your App's Performance.
• Developing Data Access Applications for Microsoft Windows CE 3.0 with ADOCE
  June 2000. Using Microsoft ActiveX Data Objects (ADO), client applications can access and manipulate data from a database server through an OLE database provider. The ActiveX Data Objects for Microsoft Windows CE (ADOCE) provides a subset of ADO for Windows CE. ADOCE adds new database functionality to the Windows CE operating system by enabling access to databases stored locally on a device and provides synchronization of data to a network database. ADOCE can be used with the Handheld PC (H/PC) and the Palm PC version 1.2. This paper provides an introduction to ADO and ADOCE, describes the Recordset and Field objects, and discusses how to use ADOCE. (9 printed pages)
• Developing Data Access Applications for Microsoft Windows CE 2.1 with ADOCE
  June 1999. Provides an introduction to Microsoft ActiveXR Data Objects (ADO) and ActiveX Data Objects for Microsoft WindowsR CE 2.1 (ADOCE) and discusses how to use ADOCE. Also discusses the Recordset and Field objects. (11 printed pages)

MS Knowledge base

• PRB: SQL Server CE Replication or RDA Fails When IIS Uses SHA1 Hashing Algorithm
  1/14/2003. Microsoft SQL Server 2000 for Windows CE Edition replication or Remote Date Access (RDA) fails when you use a secure Web site where the Microsoft Internet Information Services (IIS) server certificate uses the Secure Hash Algorithm 1 (SHA1) algorithm. The merge process returns the following error message using the Secure Sockets Layer (SSL) site: 28037 SSCE_M_HTTPSENDREQUESTFAILED : HttpSendRequest failed; HRESULT has more detail
• PRB: SQL Server CE Connectivity Application Stops Responding When Network Connection is Dropped
  2/25/2002. When a network connection is dropped, a SQL Server CE connectivity application (Replication or Remote Data Access [RDA]) stops responding for approximately 5 minutes before returning an error message
• FIX: SQL Server CE Application Fails on Pocket PC 2002 Emulator on Pentium IV Processor
  4/22/2003. When you install the Microsoft Pocket PC 2002 Software Development Kit (SDK) on a Pentium IV or on a dual processor computer, and you use eMbedded Visual Basic that targets the Pocket PC 2002 emulator, you might experience the following problems: *in the article*.
• PRB: Error Message: "28037: HttpSendRequest failed;:" Occurs with SQL Server CE Application
  4/29/2003. When you use a SQL Server CE application, you may receive the following error message: 28037: HttpSendRequest failed; HRESULT has more detail SSCE_M_HTTPSENDREQUESTFAILED The SQL Server CE Server Agent log file, Sscerepl.log, does not contain any information about Remote Data Access (RDA), or replication operations.
• INF: How to Enable Encryption Support for SQL Server CE Databases
  4/17/2003. This article describes how to enable encryption support for SQL Server CE databases.
• FIX: Reinitialized SQL Server CE Subscribers May Experience Data Loss and Non-Convergence
  4/29/2003. If a Microsoft SQL Server 2000 Windows CE Edition subscriber is marked for reinitialization, the subscriber may experience data loss and non-convergence, or both, if all the following conditions are met: *in the article*.
• FIX: SQL Server CE Delete Query with a Subquery Over a Large Table Might Stop Responding (Hang)
  /22/2003. A Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) Delete query, with a subquery over a large table, might appear to stop responding (hang). For example, a query similar to the following might take a long time to return if the tables t1 and t2 are large: delete from t1 where col1 in (select t2.col1 from t2 where t2.col2="something")
• HOWTO: Look Up Error Codes Related to SQL Server CE
  4/22/2003. A Microsoft SQL Server 2000 Windows CE application may produce a variety of error codes that originate from the ActiveX Data Objects (ADO), OLE DB, Replication, Microsoft Internet Information Server or Microsoft Internet Information Services, or Microsoft Windows programs. The SQL Server Windows CE Books Online topic about "Error Codes" does not document all of the possible error codes that may occur.
• HOWTO: Insert New Values to a Uniqueidentifier Column in SQL Server CE
  4/22/2003. SQL Server 2000 Windows CE Edition supports the uniqueidentifier data type. By default, the SQL Server NEWID function is not used in SQL Server CE to generate a unique value for the uniqueidentifier column. However, the ROWGUIDCOL attribute for a column has been overloaded to indicate that a default value should be generated. This behavior is specific to SQL Server 2000 Windows CE Edition.
• http://support.microsoft.com/default.aspx?scid=kb;en-us;314783
  4/24/2003. This guide assists you in developing your Microsoft Internet Information Server (IIS) and SQL Server security plan for mobile devices. As mobile applications begin to increase in number, a solid plan can help avoid some of the pitfalls in security. The article examines various authentication methods for IIS and SQL Server. Because SQL Server CE connects to SQL Server through IIS, this article discusses both security environments for mobile SQL Server applications.
• PRB: SQL Server CE 2.0 Does Not Support eMbedded Visual Basic 3.0
  4/17/2003. Microsoft Visual Studio .NET installs SQL Server CE 2.0. If you install Visual Studio .NET, and then you develop a Microsoft ActiveX Data Objects for Windows CE (ADOCE) application by using eMbedded Visual Basic 3.0, Microsoft CE SQL Server Control 2.0 is not listed in the References dialog box for the project in eMbedded Visual Basic 3.0. The References dialog box lists Microsoft CE ADO Control 3.1. If you add a reference to Microsoft CE ADO Control 3.1, the application fails, and you receive the following error message: "An error was encountered while running this program: ADO could not find the specified provider."
• FIX: SQL Server CE Queries with WHERE and ORDER BY Clauses May Run Slow
  4/22/2003. When you work with a SQL Server 2000 Windows CE database, certain queries with WHERE and ORDER BY clauses may run slow. For example, the following query may run slow: SELECT drug_id,label_name FROM drug WHERE label_name LIKE 'AA%' ORDER BY label_name
• HOWTO: Install SQL Server CE from the Command Line and Generate a Log File
  4/22/2003. This article describes how to install SQL Server CE from the command line and generate a Windows Installer log file. The SQL Server 2000 Windows CE Edition Web download file, SSCE_Setup.exe, provides an integrated setup of Windows CE Data Access 3.1 and SQL Server 2000 Windows CE Edition Development and Server Tools. You can also call the Windows Installer executable file (Msiexec.exe) to install SQL Server CE and the Windows CE Data Access 3.1 distribution manually. Doing so allows you to create a verbose log file (by using the /L*V switch) for troubleshooting purposes.
• INF: SQL Server 2000 Windows CE Edition Version 2.0 Readme.txt Additions
  5/1/2003. This article discusses items that have changed or that are currently not documented in the Readme.htm file for SQL Server 2000 Windows CE Edition (SQL Server CE) version 2.0.
• INF: SQL Server 2000 Windows CE Edition Version 1.1 Service Pack 1 Readmesp1.txt Addition
  3/18/2002. This article provides information about the following issues, which were not included in the Readmesp1.txt file for SQL Server 2000 Windows CE Edition (SQL Server CE) Service Pack 1 (SP1): *in the article*.
• INF: How to Obtain the Latest SQL Server 2000 Windows CE Edition 1.1 Service Pack
  9/28/2002. Service packs are the means by which Microsoft distributes bug fixes. Service packs keep a product current. Service packs include updates and may include system administration tools, drivers, and additional components, which are conveniently bundled for easy downloading. Service packs are cumulative; each new service pack contains all the fixes that are in previous service packs, as well as any new fixes. You do not need to install a previous service pack before you install the latest one.
• HOWTO: Delete Tables from a Windows CE Device from VBCE or eVB
  9/4/2002. There is currently no functionality provided on a Windows CE device by which database tables existing in the database store can be directly deleted. This article illustrates how this can be accomplished from a Windows CE Toolkit for Visual Basic (VBCE) or eMbedded Visual Basic application.
• FIX: SQL Server CE eVB CompactDatabase Unable to Fix Corrupted Database
  2/24/2003. A corrupted database may produce the following error message in the Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) Query Analyzer if you attempt to open a connection: Error: 0x80004005 E_FAIL Minor Error: (25017) Description: Unspecified Error The error code is SSCE_M_DATABASECORRUPTED (Non-database file or database file corrupted). A Microsoft embedded Visual Basic (eVB) application that uses the CompactDatabase method is not able to compact a corrupted SQL Server CE database. An attempt to call the CompactDatabase method may return the following error message: Error: -2147467259 Compact
• PRB: Error Message: "Evaluation copy has expired" May Occur After You Upgrade to Release Version of SQL Server CE
  4/12/2002. If you try to call Remote Data Access (RDA) or a Replication method by using the Microsoft SQL Server Windows CE Evaluation Edition, the following error message occurs when the evaluation copy expires: 39999: SSCE_M_EVALUATIONCOPYEXPIRED Evaluation copy has expired After you replace the evaluation edition by installing the released version of Microsoft SQL Server CE 1.1, if you try to call a RDA or Replication method, the error message still occurs.
• HOWTO: Delete a SQL Server CE Database in an Application
  4/22/2003. SQL Server 2000 for Windows CE (SSCE) supports a CREATE DATABASE command as well as Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX), OLE DB and AddSubscription methods to create a database. However, SQL Server 2000 for Windows CE does not support a DROP DATABASE command. You can use a file system object to delete a database programmatically.
• HOW TO: Store and Retrieve an Image File with SQL Server CE and eVB
  10/26/2002. This step-by-step article describes how to store an image file to a Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) database and how to retrieve the stored image from the database and reconstruct the image file. This article uses Microsoft Embedded Visual Basic as the development platform.
• FIX: SQL Server CE Is Missing Oledb.lib File for eMbedded Visual C++ 4.0
  10/16/2002. When you use Microsoft eMbedded Visual C++ 4.0 or Windows CE .NET (Platform Builder 4.0), customers cannot compile an OLE DB application or any other program that requires the Oledb.lib file.
• Searching a CEDB Database with More Than 16,000 Text Records May Hang Target Device
  10/14/2002. If you search a CEDB database that contains more then 16,000 text records, the CeSeekDatabase function may not return and the target device may seem to stop responding (hang).
• BUG: ADO CE May Cause System to Stop Responding When You Add Many Records
  4/24/2002. When you use ActiveX Data Objects for the Windows CE operating system (ADO CE) versions 3.0 or 3.1 on Handheld PC 2000, Pocket PC, or Microsoft Pocket PC 2002 devices to add records to a database, the program may stop responding. This problem typically occurs when you have a relatively large number of records in the database, and if you have created an index in the table.
• Q209050 - INFO: List of Errors Returned by ADOFiltr.dll
  10/24/2000. This article lists the error message numbers and text that are returned from ADOFiltr.dll as a result of calling either the
• Q254946 - HOWTO: Move an Access Database File from a Desktop PC to a Palm-size PC
  6/14/2001. This article demonstrates how to move a Microsoft Access database file (.mdb) from the Desktop computer to the Palm-size PC when the computers are connected through Microsoft ActiveSync.
• Q265796 - HOWTO: Run ADOCE on the Pocket PC Platform
  1/19/2001. This article describes how to target the Pocket PC platform with a project built with eMbedded Visual Basic 3.0 (eVB) that uses the Microsoft ADO for Windows CE SDK (ADOCE). A code sample is included to illustrate this functionality.
• Q301081 - HOW TO: Export a Table from Windows CE
  7/20/2001. With Microsoft(r) ActiveSync(r) 3.1 and ActiveX(r) Data Objects (ADO) for the Microsoft Windows(r) CE operating system (ADOCE) 3.1 installed, you can manually export tables from a Windows CE-based device to a desktop computer. This is convenient if you update data in a single table in a database. This article describes the steps to export tables from a Windows CE device to a Microsoft Access database.
• Q301091 - HOW TO: Import a Table into a Windows CE ADOCE Database
  7/20/2001. With Microsoft(r) ActiveSync(r) 3.1 and the ActiveX(r) Data Objects (ADO) for the Microsoft Windows(r) CE operating system (ADOCE) 3.1 installed, you can manually import selected tables from Microsoft Access .mdb files or from Open Database Connectivity (ODBC) sources to a Windows CE-based device. This is convenient if you update data in a single table in a database. This article describes how to import a table to an ADOCE database.
• Q301260 - HOW TO: Use eMbedded Visual Basic to Install SQL Server CE
  7/20/2001. This article demonstrates how to leverage the Microsoft eMbedded Visual BasicR development environment to automatically install Microsoft SQL ServerT CE on a Microsoft Windows CE-based device.

Articles at www.cegadgets.com

• Third Party Database Tools for Windows CE
  There are a phenomenal amount of database tools available for the Windows CE platform. This article briefly covers many of the currently available database tools.

Questions and Answers at www.cegadgets.com

• Which databases are available on the Windows CE platform?
  27-Mar-01.
• How can I enumerate Windows CE databases from the desktop?
  27-Mar-01.
• How do I get the name of a ADOCE table if I only have an Table ID number?
  27-Mar-01.

CodeProject

• C++ wrapper for ADOCE filtering
  1 Sep 2002. The DesktopToDevice and DeviceToDesktop functions allows you to transfer and/or synchronize MS Access databases (.mdb files) to a Windows CE device (.cdb files). These functions were designed to be used from Visual Basic, however it is still possible to call them from C++ projects by locating the function in the dll.

DEVBUZZ.COM

• WinCE database application for MS SQL Server 7 using eVB and XML
  20 Jan 2002. Need to access remote databases from eVB? Shakil shows how it is done using XML; "This article is based on one of many experimental things I do when I am bored. The concept is pretty simple. I wanted my eVB application to talk to MS SQL Sever 7 over the TCP/IP network, just the kind of the thing I do with my desktop database applications. I couldn't find any SQL Server ODBC driver for WinCE, and was not interested in writing one. But I could find different ways to connect my WinCE device to a TCP/IP based network. So I went for a different approach..."
• Pocket Access Data provider for the .NET Compact Framework
  19 Dec 2002. The omission of a Pocket Access database provider has sparked much discussion from the very first release of the .NET Compact Framework. There is still a strong need for interfacing with Pocket Access databases, both for updating applications previously written in eVB, and for any other application where installation of SQL Server CE could be considered overkill. With this in mind Peter Foot of InTheHand wrote a wrapper library around the familiar ADOCE ActiveX control.
• Forcing Database Changes from ADOCE to Commit
  ADOCE is a fantastic tool allowing eVB programmers easy access to the native data store, Pocket Access and SQL Server 2000 CE Edition databases. It has a subset of the desktop ADO objects, properties and methods and makes using databases a snap. Unfortunately, it has some flaws. One of the most disturbing problems is that if you're using ADOCE to modify data in a database, including inserts, deletes and updates, the modifications are not fully committed to the database volume until your application exits. This holds true for ADOCE 3.0 and 3.1 on the Pocket PC and HPC.
• eVB Database Viewer
  I separated the screen, into four areas. At the top is a text box and drop down list, changing positions as required. At the bottom the screen, is a label where the messages are displayed. Just above that are all the buttons used to navigate through the program, and finally in the middle taking most of the screen is the grid where all the information is displayed.
• Copy Access database files between the desktop and the Pocket PC.
  "How on earth do you write software that can copy files between the desktop and the Pocket PC?" This was the conundrum I set out to crack. There had to be a way, after all, the install programs manage it, as do various other C programs I have seen. This meant I was going to have to delve deep into that black sticky mess that all non-C programmers fear, the API's.
• SQL procedures in the CDB database??
  One disappointing aspect of using ActiveSync's Pocket Access conversion utility is that queries stored in the desktop version of an Access database are not converted. Of course, it's not likely that such a conversion would be very successful anyway since CE databases run a stripped down version of ...
• Convert Access mdb database into an eVB cdb database for emulation.
  To use an Access database in the eVB Pocket PC emulator you need to convert the database into cdb format. Here are the necessary programs for converting the MDB file into CDB file format so that you can run your ADO Database program in the emulator. In addition you can also download the following eVB code to test for the database in the emulation environment.
• eVB (eMbedded Visual Basic) and ADOCE Recordset bookmarks in Pocket PC databases.
  If you are wondering how to go about creating a bookmark within ADOCE, you have come to the right place. I am going to very quickly show you how to create a basic bookmark and I am going to use the basic code template used in the article 'Intro to Pocket PC databases'. To use some more meaningful data let us make a few minor modifications to the table and column names.
• Introduction to Pocket PC databases using ADOCE and eVB.
  Well this is always a good litmus test of your development aptitude - just how long it takes to try setting up a database in any new application environment. If you are itching to get to that stage this is just the tutorial for you. This simple tutorial will show you how to add and delete a database, create and drop tables and insert data into a table.
• Use eVB (eMbedded Visual Basic) and ADOCE to access your Pocket PC database.
  The goal of this tutorial is to use eVB to access a database that has been transferred to your Pocket PC. If you have not already imported some sample data to your Pocket PC then read Transferring data onto your Pocket PC.
• Retrieving Device Data from the Desktop with SQLink
  20 Dec 2001. If you're looking for a unique solution for transferring data from a Windows CE device to the desktop then check out this solution from Chris Tacke; "About a year ago we started development on an enterprise application for a customer that had several Pocket PC devices that were used for data collection in the field. The idea was that an administrator could assign certain "jobs" to a user through a PC interface and push those jobs to the user's device. The user would then collect field data and when they were done at the end of the day, the data would be extracted from the devices and put into a central database."
• Configuring Remote Data Access
  30 Oct 2001. In his debut column for deVBuzz Rick Winscot provides us with a detailed visual overview on how to configure Remote Data Access for SQL Server for CE. Remote Data Access on CE handhelds is implemented by a few dll's, a relay agent, IIS and SQL Server. Each piece has its own job, and putting them together can present an issue if you aren't familiar with how they work together.

Libraries

• VOConnection and VORecordset
  C++ library. Add ADO to your C++ applications without having to worry about COM. These two classes encapsulate a large percentage of the work involved in getting ADO up and running with a minimum of overhead in your application. Cleanup of connections, recordsets and COM instances is automatic.