HOW TO: Change the Temp Database Location in SQL Server CE (317032)

SUMMARY

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:

• Interim data created during a transaction before the transaction completes, including replication data.
  
  -or-
  
  
• 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

SQL Server CE implicitly wraps Data Manipulation Language (DML) statements in a transaction; therefore, large DML statements (UPDATEs or DELETEs) also fail. Use of an explicit transaction also causes a database to grow.

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:


back to the top

How to Specify a Tempdb Location with ActiveX Data Objects for Microsoft Windows CE (ADOCE)

Connection strings that contain a list of property values separated by semi-colons control the ADOCE operations. You can specify a temporary database location by adding the following sub-string to the end of the connection string:

";SSCE:Temp File Directory = temp_database_dir"  

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.

back to the top

How to Specify a Tempdb Location for Remote Data Access and Replication

Remote Data access (RDA) and Replication operations are controlled by connection strings that contain a list of property values separated by semi-colons. You can specify the temporary database location by adding the following sub-string to the end of the connection string:

";SSCE:Temp File Directory = temp_database_dir" 

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:

• The RDA_Object.Pull method.
• The RDA_Object.Push method.
• The RDA_Object.SubmitSQL method.

The following partial code fragment illustrates how you use the OLEDBConnectionString property in RDA:

' 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" 

For Replication, you must include the temporary database location for all methods that use a SubscriberConnectionString property. This includes:

• 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.

The following partial code fragment illustrates how you use the SubscriberConnectionString property in Replication:

' 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
 ceRepl.AddSubscription CREATE_DATABASE  

back to the top

How to Specify a Tempdb Location for OLEDB Operations

OLEDB Operations that can accept properties in preparation for loading the SQL Server CE engine are:

• IDBDataSourceAdmin::CreateDataSource
• IDBProperties::SetProperties

You must pass the temporary database location to either IDBDataSourceAdmin::CreateDataSource or IDBProperties::SetProperties as an element of the connection properties, in the same way that you pass the regular database location.

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.

Validation and Error Handling

The client application verifies the temporary database location provided when the application passes the location to the operating system. SQL Server CE does not try to pre-verify the string. If you provide a UNC path, the success of the UNC path is up to the operating system. If the operating system can handle UNC paths and if the target directory is available, SQL Server CE succeeds at creating the temporary files in the specified location.

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.

back to the top

Limitations of Specifying a Tempdb Location

There is one temporary database per instance of the SQL Server CE engine. Therefore, a single copy of the temporary database is common for all connections to the same instance of the SQL Server CE engine.

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.

back to the top