FIX: Some rows are deleted when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option in SQL Server 2005 Compact Edition and in SQL Server Compact 3.5

In Microsoft SQL Server 2005 Compact Edition or in Microsoft SQL Server Compact 3.5, when you repair a database by using the Repair method together with the RepairOption.RecoverCorruptedRows option, some rows are unexpectedly deleted.

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site: Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

There are no prerequisites for installing this hotfix.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace a previously released hotfix.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

SQL Server Compact 3.5 for Windows CE 5.0 on Armv4i-based devices other than pocket PC devices

SQL Server Compact 3.5 for Windows CE 5.0 on Armv4i-based pocket PC devices

SQL Server 2005 Compact Edition for Windows CE 4.0 on Armv4-based devices other than pocket PC devices

SQL Server 2005 Compact Edition for Windows CE 4.0 on Armv4-based pocket PC devices

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

How to install this hotfix

To install this hotfix, follow these steps:

1. Download the hotfix for your system.
2. On the device, uninstall the existing version of SQL Server 2005 Compact Edition or of SQL Server Compact 3.5.
3. Extract the .cab files from the hotfix package.
4. On the device, install the three .cab files.

More information for developers

You must follow these steps to replace the DLLs that your project uses with the DLLs from the hotfix package:

1. Extract the SYSTEM~1.004 file from the .cab file for your system.
   
   The following table lists the .cab file from which you can extract the SYSTEM~1.004 file.
   Collapse this tableExpand this table

   Platform	Hotfix name	.cab file
   SQL Server Compact 3.5 for Windows CE 5.0 on Armv4i-based devices other than pocket PC devices	SQL_CE_35SP1_wce5_ARMV4i_3_5_5692_5	Sqlce.wce5.armv4i.cab
   SQL Server Compact 3.5 Windows CE 5.0 on Armv4i-based pocket PC devices	SQL_CE_35SP1_wce5_PPC_ARMV4i_3_5_5692_5	Sqlce.PPC.wce5.armv4i.cab
   SQL Server 2005 Compact Edition for Windows CE 4.0 on Armv4-based pocket PC devices	SQL_CE_31_WCE400_PPC_5300_9	Sqlce30.PPC.wce4.armv4.cab
   SQL Server 2005 Compact Edition for Windows CE 4.0 on Armv4-based devices other than pocket PC devices	SQL_CE_31_WCE400_5300_9	Sqlce30.wce4.armv4i.cab

2. Rename the SYSTEM~1.004 file as the System.Data.SqlServerCe.dll file.
3. Replace the System.Data.SqlServerCe.dll file that is installed on your system with the new System.Data.SqlServerCe.dll file that you obtained in step 2.
   
   If you are using SQL Server 2005 Compact Edition, replace the file in the following location with the file that you obtained in step 2:
   %ProgramFiles%\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll
   If you are using SQL Server Compact 3.5, replace the file in the following location with the file that you obtained in step 2:
   %ProgramFiles%\Microsoft Sql Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll
4. If your project uses the System.Data.SqlServerCe.dll file that is not in the location that step 3 specifies, replace the System.Data.SqlServerCe.dll file in that location.

Changes included in this hotfix

After you apply this hotfix, the RepairOption enumeration is enhanced as follows. Please review carefully.

• RecoverAllOrFail This is a new option introduced in this hotfix. If the Repair method is invoked by using the RecoverAllorFail value, the recovery will be successful only if there is no data loss. If data loss is detected, the recovery is aborted, and an exception is thrown. This is the preferred option to make sure that the database repair is completed successfully and that there is no data loss. It is the most effective option to help protect against data loss and corruption of the repaired database.
• RecoverAllPossibleRows This is a new option introduced in this hotfix. If the Repair method is invoked by using the RecoverAllPossibleRows value, the database will try to read data from the corrupted pages. This potentially results in more data being recovered. However, this option does not guarantee that the recovered data will be free of any kind of corruption. So even if the next Verify call on the repaired database returns a value of true, the database might contain logical corruption. For example, a foreign key value may be present without the corresponding primary key value. If this occurred, the foreign constraint schema definition in the target database would fail and be missing. Recovery is performed in this manner to improve performance.
• DeleteCorruptedRows If the Repair method is invoked by using the DeleteCorruptedRows value, all corrupted pages are discarded from the database. This may result in significant data loss. With this option, even if the next Verify call on the repaired database returns a value of true, the database might contain logical corruption. For example, a foreign key value may be present without the corresponding primary key value. If this occurred, the foreign constraint schema definition in the target database would fail and be missing. Recovery is performed in this manner to improve performance.
• RecoverCorruptedRows This option is not considered effective, and should not be used. Please use RecoverAllPossibleRows instead.

Notes

• The Repair method does not guarantee complete data recovery for every suspect database. Some forms of data corruptions cannot be repaired completely, regardless of the Repair option that is selected by the application.
• Regardless of the repair option that is selected, the Repair method may write details about any database inconsistency into a log file. This file is written to the same directory that contains the source database .sdf file. The application or a database administrator should examine this log file to determine whether the resulting database is valid. This is an important step when the application does not use the RecoverAllOrFail option.
• When the application does not use the RecoverAllOrFail option, it is the responsibility of the application or of database administrator to validate the resulting database. This is accomplished by examining the log file to determine whether the database is reliable.
• If the application or the user cannot validate the resulting database, restore the database from a backup, or manually re-construct the database. You should do this before you use the database in production.

For more information about the Incremetal Servicing Model, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: