SQL Server Compact error: Unable to load DLL 'sqlceme35.dll'. The specified module could not be found

Question

I'm developing a Windows Forms application using Visual Studio 2008 C# that uses an SQL Server Compact 3.5 database on the client. The client will most likely be 32 bit Windows XP or Windows Vista machines. I'm using a standard Windows Installer project that creates an MSI file and setup.exe to install the application on a client machine. I'm new to SQL Server Compact, so I haven't had to distribute a client database like this before now. When I run the setup.exe (on new Windows XP 32 bit with SP2 and Internet Explorer 7) it installs fine, but when I run the application I get this error:

Unable to load DLL 'sqlceme35.dll'. The specified module could not be found

I spent a few hours searching for this error already, but all I could find were issues relating to installing on 64 bit Windows and none relating to normal 32 bit that I'm using.

The install application copies the all the dependent files that it found into the specified install directory, including the System.Data.SqlServerCe.dll file (assembly version 3.5.1.0). The database file is in a directory called 'data' off the application directory, and the connection string for it is

<add name="Tickets.ieOutlet.Properties.Settings.TicketsLocalConnectionString"         connectionString="Data Source=|DataDirectory|\data\TicketsLocal.sdf"  providerName="Microsoft.SqlServerCe.Client.3.5" />

Some questions I have:

• Should the application be able to find the DLL file if it's in the same directory, that is, local to the application, or do I need to install it in the GAC? (If so, can I use the Windows Installer to install a DLL file in the GAC?)
• Is there anything else I need to distribute with the application in order to use a SQL Server Compact database?
• There are other DLL files also, such as MS interop for exporting data to Excel on the client. Do these need to be installed in the GAC or will locating them in the application directory suffice?

Answer 1

You don't need it to be in the GAC for SQL Server Compact to run, and it will pick them up from the application directory. There are several ways to deploy an SQL Server Compact project. The two main ways are:

1. Deploying the SQL Server Compact redistributable installer with your project, but this way is painful and also can be unistalled by the end user, or upgraded by Windows updates and breaking your application.

2. Including the DLL files in your application folder. Depending on the features of SQL Server Compact you are using (replication or whatever), there is a handful of DLL files to deploy in your application folder.

If you have SQL Server Compact installed on your machine, they are most likely located at "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5". They can be added to the project in Visual Studio and then set their project output type to "copy always". And the main reference to System.Data.SqlServerCe that you have in your project references should have copy local set to true.

• sqlceca35.dll
• sqlcecompact35.dll
• sqlceer35en.dll
• sqlceoledb35.dll
• sqlceqp35.dll
• sqlcese35.dll

If you have these all set, then in your installer project all you have to include is the project output of this project and you're good. In my opinion this is the only way to go. It is a simple deployment, of a couple of files and you are in control of what DLL versions your application uses.

I hope that helps.