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

Question

Hi - I'm developing a Winforms application using Visual Studio 2008 C# that uses a SQL compact 3.5 database on the client. The client will most likely be 32 bit XP or Vista machines. I'm using a standard Windows Installer project that creates an msi file and setup.exe to install the app on a client machine. I'm new to SQL 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 IE 7) it installs fine but when I run the app I get the error below:

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

I spent a few hours searching this error already but all I can find are issues relating to installing on 64 bit Windows, none relating to normal 32 bit that I'm using. The install app copies the all the dependant 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 app be able to find the dll if it's in the same directory i.e. local to the app, or do I need to install it in the GAC? (If so cam I use the Windows Installer to install a dll in the GAC?)
• is there anything else I need to distribute with the app in order to use a Sql Compact database?
• there are other dlls 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?

TIA,

Ciaran.

Answer 1

This should help, at least with your first two questions: http://msdn.microsoft.com/en-us/library/aa983326(VS.80).aspx

In general, I think you should not install anything in the GAC for a single application.

Answer 2

Here are some links that provide a solution to the problem and an explanation also

http://blogs.msdn.com/sqlservercompact/archive/2007/10/26/troubleshooting-can-t-load-sqlce-dll.aspx

http://blogs.msdn.com/laxmi/archive/2008/10/27/sqlceexception-on-application-s-first-use-of-sql-ce.aspx

http://blogs.msdn.com/laxmi/

Hope this helps

Answer 3

You don't need it to be in the GAC for sql compact to run and it will pick them up from the application directory. There are several ways to deploy a sqlcompact project the two main ways are:

1. Deploying the sqlCompact redist. installer with your project, but this way is a pain in the ass and also can be unistalled by the end user, or upgraded by windows updates and breaking your app.

2. Including the .dlls in your application folder. Depending on the features of sqlcompact you are using (replication or whatever) there a handful of .dll's to deploy in your application folder.

IF you have sql 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 files and you are in control of what dll versions you app uses.

Hope that helps.