• Article

If the instance of SQL Server that you are connecting to contains ANSI data, the data is converted to Unicode before being stored in the SQL Server Compact 3.5 database. Depending on the code pages of the computer that is running SQL Server and the computer that is running IIS, conversion errors can occur. The following information helps ensure that the data is converted correctly.

ANSI to Unicode Conversions in the SQL Server Compact 3.5 Server Agent

The SQL Server Compact 3.5 Server Agent that is installed on the computer that is running Microsoft Internet Information Services (IIS) converts ANSI data in SQL Server to or from Unicode for the SQL Server Compact 3.5 database. To ensure correct translation, the SQL Server Compact 3.5 Server Agent determines in which code page the ANSI data is stored before you perform ANSI to Unicode conversions. The following information is considered:

  • If no other settings are available, the ANSI code page of the computer that is running IIS is used.

  • If you are using an instance of SQL Server 2005 or a later version, the SQL Server Compact 3.5 Server Agent uses the code page of the computer that SQL Server is running on. When each instance of SQL Server is set up to correctly identify the ANSI data that is being stored, the correct translation is automatically selected for each connection.

  • If the translation does not give the correct value for the data that is being stored, you can override the code page by setting a registry key to a specific code page.

Creating a Registry Key to Override the Code Page

The following steps show how to create a registry key that will override the code page.

Incorrectly editing the registry can severely damage your system. Before making changes to the registry, you should back up any valued data on the computer.

  1. If it does not already exist, create the following registry key:

    HKLM\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Transport

  2. Under the HKLM\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\Transport key, create a DWORD key value. The name of the key value must start with the local path associated with the IIS virtual directory for SQL Server Compact 3.5. For example, if Sqlcesa35.dll is located in the folder C:\Inetpub\Sqlce\, you must name the registry value C:\Inetpub\Sqlce\OVERRIDE_SERVER_CP. The data value of the DWORD key must be a value of the code page. For example, the following registry key value is for the English code page:

    Key: HKLM\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5\TransportValue: C:\Inetpub\Sqlce\OVERRIDE_SERVER_CP 1252

  3. After you create the registry entry, you must restart IIS. This is important because the SQL Server Compact 3.5 Server Agent reads the registry key only when the SQL Server Compact 3.5 Server Agent is first loaded by IIS.