Symptoms
Assume that you have specified the flush interval in the maximum number of seconds in the connection string before committed transactions are flushed to disk in Microsoft SQL Server Compact 4.0. In this situation, the committed transactions may take much longer time than the flush interval to be flushed to disk or may not even be flushed to disk. Additionally, data loss occurs if there is an abnormal program termination.
Resolution
The hotfix that resolves this problem is included in an on-demand hotfix update package for SQL Server Compact 4.0 Service Pack 1.
For more information about how to obtain this on-demand hotfix update package for SQL Server Compact 4.0, click the following article number to view the article in the Microsoft Knowledge Base:
2960153 On-demand hotfix update package for SQL Server Compact 4.0 Service Pack 1
Workaround
To work around this issue, use CommitMode.Immediate to flush the changes immediately instead of relying on the flush interval. The following sample code demonstrates this technique:conn.ConnectionString = "Data Source = c:\temp\SalesData.sdf; "
conn.Open()
Dim tx As SqlCeTransaction
tx = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
com = conn.CreateCommand()
com.Transaction = tx
Dim a As Integer
a = 101
While (a <= 1000)
com.CommandText = "insert into Orders values (" + Convert.ToString(a) + ", 'best', 200)"
com.ExecuteNonQuery()
a = a + 1
End While
tx.Commit(CommitMode.Immediate)
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
