SQL Server 2000 Windows CE Edition 1.0 and 1.1 frequently asked questions

View products that this article applies to.

Expand all | Collapse all

INTRODUCTION

Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) is the compact database for rapidly developing applications that extend enterprise data management capabilities to devices.

This article discusses questions and answers for frequently asked questions (FAQs) about SQL Server 2000 Windows CE Edition 1.0 and 1.1.

Back to the top

Q1: SQL Server CE Books Online mentions that you can put the SQL Server Agent CE Server Agent (Sscesa10.dll) in multiple locations and that you must register the Sscesa10.dll file by using Regsrv32.exe after the file is copied to a folder. Does re-registering the Sscesa10.dll file in another folder overwrite the existing reference in the registry with the new one and point to the DLL in the new folder?

A1: The Sscesa10.dll) file does not overwrite any registry entries and can be registered in multiple locations without any conflicts. This behavior is different from other Microsoft Component Object Model (COM) components. Other COM components leave some entries, including InProcServer32, pointing to the last registered component when they are registered in multiple locations.

Q2: What are the benefits of multiple virtual directories with separate copies of the Sscesa10.dll file?

A2: When you have multiple applications that use replication and Remote Data Access (RDA), you want to create multiple virtual directories. You want each virtual directory to point to its own base folder with a different copy of the Sscesa10.dll file. You want separate virtual directories so that the two connectivity solutions (replication and RDA) do not contend with the same available threads. If replication users use the Sscesa10.dll file to synchronize, and if RDA users use the Sscesa10.dll file at the same time, at some point one user may be waiting for the other to finish. SQL Server CE is multithreaded, but it does have a limit. Therefore, if you set up two different Sscesa10.dll files, each can handle its own set of users.

You can also implement different security schemes if you use two different virtual directories, even if the virtual directories point to the same SQL Server Agent.

Q3: How do I migrate a SQL Server database from SQL Server to SQL Server CE?

A3: You can use the RDA Pull method with the no-tracking option to pull all tables to your SQL Server CE database, together with the data. If you have default values on any columns, referential integrity, or any indexes, you must run the correct data definition commands to create these properties. For more information, see the Introducing Remote Data Access, Planning for RDA, and Implementing RDA topics in SQL Server CE Books Online.

RDA does not pull referential integrity or multiple tables at the same time. RDA pulls only the primary key (PK) index and the table that is specified in the Pull method. You must add back all the referential integrity to the local version of the table by using the ALTER TABLE command.

Both RDA and replication can copy tables from SQL Server to the SQL Server CE database on the device. This is the "easiest" method to use because this method automatically performs data type mappings for you. Replication only works with Microsoft SQL Server 2000. If your requirement is simple, RDA is for you. However, if you have the following requirements, consider using the replication method:

You must change data on both the server and the device.
You want to have that data merge.
The server is running SQL Server 2000.

Replication requires more configuration on the server, because you must publish the SQL Server data first.

You can also run SQL statements on the local database that is on the device to create and to insert data. The grammar for SQL Server CE is a subset of SQL Server.

Q4: Why is the space that is taken by deleted records not freed unless I run a compact process on the SQL Server CE database?

A4: There are two issues:

Space is not reused by SQL Server CE during a transaction. The locking scheme that the engine currently uses prevents the reuse of records that are deleted in a transaction until that transaction is committed. If you do not manually start transactions, and if you put several records in a database, and then if you delete those records, the space remains unused. If you add more records, that space is used again. If you compact the database, you eliminate unused space.
SQL Server CE does not make databases smaller unless you compact the database. You must have two times the database size to compact the database because the compact action creates a new database. You cannot compact in place in SQL Server CE. To reduce the size of your database, you must run a compact process. When new records are added to the database, the database grows. If you delete those records, the space is then empty in your database but the database does not become smaller. When you add more records, the empty space in your database is used until the database must grow again. SQL Server CE databases do not become smaller during typical operation. You must use a compact process regularly to make sure that the database is not taking up extra space and that the database does not have internal errors.

Q5: When I set up merge replication on SQL Server 2000, you receive an Agent Error message that states the following:

"@start_time_string is not known by sp_MSadd_snapshot_history"

If I add a dummy parameter to the stored procedure in the distribution database, the merge works correctly.

A5: You receive this error message when you use an earlier (beta) version of SQL Server 2000.

Q6: Can I use Microsoft eMbedded Visual Basic to create the .sdf database on my desktop?

A6: No, some technical problems are associated with the use of eMbedded Visual Basic to create the .sdf database on a desktop computer.

Q7: How can I avoid time-out error messages when I call the RDA Pull method?

A7: To extend the connection time-out period, pass Connect Timeout=Time in the connect string that you have. For example, for 300 seconds, the connect string would look similar to the following:

ce.Pull "EW", "SELECT * FROM customers where cust_id='007'"
"Provider=SQLOLEDB; Data Source=mySQLServer; Initial 
Catalog=NorthWind; user id=sa; password=; Connect 
Timeout=300;", TRACKINGOFF

You can then fine-tune the connect string to an optimum value based on your network.

Q8: The RDA Pull method works correctly in emulation. However, if I run the same code from my device, I receive error 80072EFD "Pull Failed". My device is connected through a serial cable and a Microsoft ActiveSync connection is working. When I run http://myserver/sharename/sscesa10.dll in Microsoft Internet Explorer on the device, the following text appears:

"The page you are looking for cannot be found"

instead of the following:

"Body"

A8: The error message indicates that you cannot connect to your Microsoft Internet Information Server (IIS) server from your device. If your device is connected through ActiveSync, either RDA or replication may not work correctly because ActiveSync does not give you full network access. You must have some sort of network connectivity, either through Remote Access Service (RAS) with a standard or wireless modem or with a direct network connection LAN adapter (or wireless LAN adapter).

Q9: How do I create an index on a SQL Server CE table?

A9: The easiest way to create an index is to use a SQL statement. For example:

create table t1 (col1 int); create index t1_index on t1 (col1);

Q10: How fast is SQL Server CE performance versus the native CE database (.cdb) store on the device?

A10: SQL Server CE is much faster than the .cdb store in many ways. For example, SQL Server CE is faster at creating and navigating indexes, performing joins, and other data manipulation statements. Some customers report up to a 90 percent performance improvement over .cdb on the same hardware. Results vary based on the scenario, but you may notice the difference immediately.

Q11: How do I compact a database?

A11: The following is eMbedded Visual Basic code that compacts a database to another file and then moves it back. As the compact process works, it creates a second database and leaves the first one at the original size. The code deletes the first database, and then the code copies back the new second database:

Private Sub Compact_Click() 
Dim eng As SSCE.Engine 
Set eng = CreateObject("SSCE.Engine.1.0") 
cn.Close ' Close connection to db. Must be closed to work. 

eng.CompactDatabase "Provider=microsoft.sqlserver.oledb.ce.1.0;Data
Source=\Dummy.sdf", "Provider=microsoft.sqlserver.oledb.ce.1.0;Data
Source=\Dummy2.sdf"

fs.Kill "\Dummy.sdf" ' Delete old db
fs.MoveFile "\Dummy2.sdf", "\Dummy.sdf" 'Move compacted db to original location

End Sub

Q12: How do I store images on the SQL Server CE database?

A12: SQL Server CE supports the image data type. However, most Windows CE devices have limited memory. Therefore, you must be careful not to use all your available storage space.

If you program to the OLE DB layer, you can use either the ILockBytes interface or the IsequentialStream interface to move binary large object data in and out of the database.

Microsoft ActiveX Data Objects for Windows CE (ADOCE) also supports binary large objects. View the GetChunk method and the AppendChunk method on the recordset objects in ADOCE. With the GetChunk method and the AppendChunk method, you can read from and write to a large object column in the database. You can use an array of bytes inside the variant through ADOCE C++.

You must make sure that you use an updateable cursor in SQL Server CE to store binary large objects. In other words, use a base table cursor so that the AddNew function call works correctly. For example:

rs.open "tablename", cn, adopendynamic, adlockoptimistic, adcmdtabledirect

Q13: Does SQL Server CE support the AddNew function and the Update function? I keep receiving a "Not Supported" error message.

A13: SQL Server CE does support the AddNew function and the Update functions but only on base table cursors. Query result sets are not updateable in the current version of SQL Server CE.

To establish a base table, you must specify the adcmdtabledirect flag as follows:

rs.open "basetablename", cn, adopendynamic, adlockoptimistic, adcmdtabledirect

Q14: What is the SQL Server CE equivalent to "SELECT @@IDENTITY" in SQL Server?

A14: You can retrieve the new identity value through ADO or OLEDB when you perform inserts through the cursor (for example, by using the rs.addnew function). However, this method does not work correctly with INSERT INTO queries.

Because SQL Server CE can only perform insertions through base table cursors, the syntax looks similar to the following:

rs.open "basetablename", adopendynamic, adlockoptimistic, adcmdtabledirect
rs.addnew
msgbox rs(0)
rs(1) = 5
msgbox rs(0)

If column 0 is your identity column, you may see the new value already in the row. Both message box calls display the value. This syntax is the closest equivalent to "SELECT @@IDENTITY" in SQL Server CE.

If you are using INSERT statements, you can architect your application. For example, you could use "SELECT MAX(identcolumn) FROM TableName". This method is imperfect, but because most SQL Server CE applications are single-user, the syntax is workable.

Q15: Does RDA support Push or Pull conflict resolution?

A15: There is no conflict resolution with RDA. For RDA, a better name for "conflicts" is "errors." For example, when you perform an RDA Push operation, and there is an error, the row with the error is returned to your RDA Push method with the tracking error table and is removed from the table where the error occurred. In the tracking table, RDA gives you the data that was in your table in addition to the error that occurred.

Errors may occur in many ways on a Push operation. For example, errors may occur with IIS, SQL, OLEDB, and others. One common error with a new product is that foreign key constraints are not pulled to the CE database. Therefore, when the Push operation occurs, the foreign key is violated and an error record is returned to your error table. The error message informs you that you must add these constraints to your CE database and then you must retry the record.

If there are errors with the Pull method, you must check for the SSCEErrors collection that is returned. The SSCEErrors collection contains a set of SSCEError objects. The SSCEErrors collection is created when a SQL Server CE Replication, RemoteDataAccess, or Engine object method call fails.

Q16: How do I create a SQL Server CE database file on the desktop so that I can distribute (download) to many devices instead of a single table Pull operation?

A16: There is currently no direct Win32 mechanism that you can use to create a SQL Server CE database on the desktop computer and then use it on the device. However, you can create the database in the desktop emulation environment or on a Microsoft Windows CE device, copy the database to a Win32-based computer, and then copy the database to other Windows CE devices as necessary.

Q17: Does setting my ADO Recordset to 'nothing' reclaim any memory?

A17: Generally, the answer is "yes." However, Microsoft recommds that all your applications use set rs=nothing and set cn=nothing when you are finished using them. Database engines can take a fair amount of memory, and it is best not to leak memory.

Usually, Microsoft Visual Basic eventually releases the memory. However, there have been some cases where the memory is not released quickly enough and you can run out of memory on the device, especially if you have a particularly bad memory leak.

Q18: Does SQL Server CE encryption work for emulation mode? I do not see a folder for emulation under encryption in my SQL Server CE installation.

A18: Encryption is not supported in the emulation.

Q19: Can I create a SQL Server CE database .sdf file on the emulator and freely use it on any other device?

A19: Yes, a database that is created in the emulator is compatible with the devices because the database is based on the same set of operating system sort tables.

Q20: How do I always refresh SQL Server CE and Data Access components on the device?

A20: Make sure that you set the project properties in your eMbedded Visual Basic application. To do this, follow these steps: