Article ID: 317032
Microsoft SQL Server 2000 Windows CE Edition version 1.1 (SQL Server CE) creates a temporary database that it uses for storing temporary data such as:
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:
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:
(http://support.microsoft.com/kb/305349/EN-US/ )FIX: SSCE:Temp File Directory Property Allows You to Change the Temp Database Location for SQL Server CE
The location of the temporary database path inside the connection string is not important, as long as you properly separate each property value with semi-colons.
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.
The location of the temporary database path inside the connection string is not important as long as each property value is properly separated by semi-colons.
For RDA, you must include the temporary database location for all methods that use an OLEDBConnectionString property. This includes:
For Replication, you must include the temporary database location for all methods that use a SubscriberConnectionString property. This includes:
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:
If the temporary database location is invalid, SQL Server CE does not attempt to write the temporary database to the default location. SQL Server CE customers who want automatic fail-over operation must trap the new return value in their own and then retry the SQL Server CE operation without the temporary database option in their connection string.
SSCE_M_INVALIDTEMPPATH 25090 "The temp file folder location that you specified is not valid for this device"
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: June 19, 2014 - Revision: 2.0