IN THIS TASK
- Interim data created during a transaction before the transaction completes, including replication data.
- Interim result sets that are created during a query.
Several SQL Server CE users have large databases that create large volumes of temporary data during synchronization. Large databases cause the SQL Server CE application to fail because of insufficient storage space on the default storage device for both the main database file and the temporary database file.
The temporary database grows every time a sort operation occurs. A sort operation is not limited to but includes:
- Index creation
- Order by
- Group by
You can delete a temporary database whenever the operating system allows a deletion. If a file is open in SQL Server CE and SQL Server CE is using the file, the operating system disallows a deletion.
By default, the operating system creates temporary database files in the Temp folder on the Windows CE device. SQL Server CE automatically deletes the temporary file after you successfully remove the SQL Server CE engine. However, an abnormal termination of a SQL Server CE application may leave files in the Temp folder. If files remain in the Temp folder, you must perform a periodic cleanup of the temporary files.
Even if you move your user database (.sdf) file to a flash card (to store large databases), the temporary database files are created in the Temp folder. This creation of temporary files causes problems for customers who store large databases and perform intensive queries with sort operations.
When the Server CE engine starts, it creates a temporary database. The first database operation that causes the SQL Server CE engine to start must include the location of the temporary database.
This article outlines how you can change the temporary database location for SQL Server CE.
Before you change the tempdb location in your code, you must apply the fix mentioned in the following Microsoft Knowledge Base article:
";SSCE:Temp File Directory = temp_database_dir"
For ADOCE, you must include the temporary database location for the Connection.Open method.
You can also specify the temporary database location in the source connect string for the Engine.CompactDatabase method.
";SSCE:Temp File Directory = temp_database_dir"
For RDA, you must include the temporary database location for all methods that use an OLEDBConnectionString property. This includes:
- The RDA_Object.Pull method.
- The RDA_Object.Push method.
- The RDA_Object.SubmitSQL method.
' Declare the SQL Server CE ActiveX Control RDA Object Control.
Dim ceRDA As SSCE.RemoteDataAccess
' Create the RDA Object
Set ceRDA = CreateObject("SSCE.RemoteDataAccess.1.0")
' Set RDA properties
ceRDA .InternetURL = "http://www.northwindtraders.com/sqlce/sscesa10.dll"
ceRDA .InternetLogin = "MyInternetLogin"
ceRDA .InternetPassword = "MyInternetPassword"
ceRDA .LocalConnectionString = "Data Source=\NorthwindRDA.sdf;SSCE:Temp File Directory =\"
' Push the tracked SQL Server CE table changes back to the SQL Server table
ceRDA .Push "Customers", "Provider=sqloledb;Data Source=SampleServer;Initial Catalog=Northwind;user id=SampleUser;password=SamplePassword"
- The REPL_Object.Initialize method.
- The REPL_Object.AddSubscription method.
- The REPL_Object.Run method.
- The REPL_Object.ReinitializeSubscription method.
- The REPL_Object.DropSubscription method.
' Declare the Replication Object.
Dim ceRepl As SSCE.Replication
' Create the Replication Object
Set ceRepl = CreateObject("SSCE.Replication.1.0")
' Set Internet properties
ceRepl.InternetURL = "http://www.northwindtraders.com/sqlce/sscesa10.dll"
ceRepl.InternetLogin = "MyInternetLogin"
ceRepl.InternetPassword = "MyInternetPassword"
' Set Publisher properties
ceRepl.Publisher = "SamplePublisher"
ceRepl.PublisherDatabase = "Nwind_SQLCEReplDemo"
ceRepl.Publication = "SQLCEReplDemo"
ceRepl.PublisherSecurityMode = DB_AUTHENTICATION
ceRepl.PublisherLogin = "MySqlPublisherLogin"
ceRepl.PublisherPassword = "MySqlPublisherPassword"
' Set Subscriber properties
ceRepl.SubscriberConnectionString = "data source=\NorthwindRepl.sdf;SSCE:Temp File Directory =\"
ceRepl.Subscriber = "SQLCE Sub #1"
' Create the new anonymous subscription
The property name is: DBPROP_SSCE_TEMPFILE_DIRECTORY
The property description is: "SSCE:Temp File Directory"
The OLEDB property set for the tempfile directory is: DBPROP_SSCE_DBINIT
The property value is: A string that can contain any valid path specification.
If the directory name is invalid, or if the file name is invalid, or if the directory cannot be opened for writing, SQL Server CE fails with this error message:
You can specify the temporary database location only once when SQL Server CE starts. After an instance is running and already has a tempdb, SQL Server CE ignores any attempts to choose another temporary database location.
You must use the same temporary database location for every operation in your application that can trigger the database engine to load, even operations where the application writer is sure that the engine is already loaded. Use of the same temporary database location protects you against unexpected results from subsequent application restructuring, and from changes in the internal operation of SQL Server CE. You must not create applications that rely upon side-effects in the way that temporary databases are loaded. Microsoft reserves the right to change such incidental operation in future versions of SQL Server CE, both for single instances of the engine and for when there may be more than one instance of the engine, and is not responsible for maintaining compatibility with the current behavior.
Article ID: 317032 - Last Review: Jun 19, 2014 - Revision: 1