Several users running various x64 version of Windows are facing problems when trying to develop or deploy solutions based on SQL Compact. The problems comes from the fact the SQL Compact is only supported in 32 bit WoW (Windows on Windows) mode on the x64 platform. This gives problems in two situations: When trying to run .NET Framework based applications, and when trying to use the SQL Compact Synchronization Agent (sqlcesa3x.dll) on x64 version of Internet Information Services (IIS).
Making .NET applications that depend on SQL Compact work on a x64 system
Any new project you start in Visual Studio is by default targeted to the "Any CPU" platform:
This means that unless modified either by changing the build configuration (if you have built the application yourself) or modifying the .exe file by using corflags.exe (if you have not built the application yourself), the application will execute under the default .NET Framework, which is a x64 version on the Framework on the x64 platform. Since your application depends o several unmanaged DLL files, namely the SQL Compact DLLs, it will not be able to use SQL Compact and fail.
Here is how to change the target platform from within Visual Studio:
(If you do not see this drop down, go to Tools, Options, Projets and Solutions, and make sure "Show advanced build configurations" is checked)
Select the Configuration Manager from the drop down above:
Select <New...> and selcet x86 from the top drop down:
(and click OK and Close)
Now you configuration look like this:
You are good to go, since your application will now be forced to use the 32 bit version of the .NET Framework always, even on a x64 platform.
Forcing 32 bit (if you do not have the source code)
In order to do this, use the .NET Framework SDK tool called corflags.exe.
Launch a Visual Studio command prompt, and you can use corflags.exe:
The flag you need to set is the 32BIT flag, so use a command line like this against your .exe file:
corflags C:\myprogram.exe /32BIT+
Your application will now be forced to use the 32 bit version of the .NET Framework always, even on a x64 platform.
Runnning the Replication Agent on a x64 version of IIS
The x64 version of IIS runs by default in x64 mode. This caused many problems for 32 bit ISAPI DLLs, for example sqlcesa30.dll and sqlcesa35.dll - the SQL Compact replication agent DLL. You need to configure an application pool to run in 32 bit mode. In IIS 7 this is quite simple, as demonstrated here.
In IIS 6, enabling this is slightly more complicated, and forces all application pools on your IIS server to run in 32 bit mode. Windows Server 2003 Service Pack 1 is required for this to work. How to enable this is documented here.