I just created (well been messing around for a week now with various parts of that installer) an installer for a VSTO project privately installing the compact dlls. Everything fine until I deploy to this clean machine and the customized vsto document in a different location than the .vsto manifest (and the rest of the dlls).

In that case you get the darn "Unable to load sqlceme35.dll" crash. I tested this and one solution is to move the dlls to a location that can be found in the system path (such as System32) but this makes the private install excercise a bit useless as the advantage of having the private install was that I didn't have to involve IT in any way.

If you need more information on how to privately deploy SQL Compact with your application I recommend reading Steve Laskers blog item: http://blogs.msdn.com/stevelasker/archive/2008/10/22/privately-deploying-sql-server-compact-with-the-ado-net-entity-provider.aspx

Let me try to explain how the VSTO scenario works:

The VSTO scenario starts with having an Office document (Word document, Excel workbook, PowerPoint slide set) where you can ‘attach’ a document customization. The document customization ‘extends’ the document to make it possible to do additional things often close related to the document such as providing a Ribbon Bar for that specific document where you can hit the DoSomething button and do something with the SQL Compact Databases. The VSTO document customization is therefore able to provide everything you can do with .NET.

If you translate this to applications and assemblies you get the following scenario:

Now, for those of us still reading, we are now at the “Document-level customization assembly” that calls into a SQLCe function. And here it goes wrong, providing you the  the ‘unable to load sqlceme35.dll’ feature.

Next we translate this into a deployment scenario where:

- Word is located in C:\Program Files\Microsoft Office\ …. Msword.exe
- The Document-level customization assembly customization.dll (plus other stuff) and
- The SQL Compact Dll’s are also located in C:\Program Files\<manufacturer>\<appname>\ (privately deployed)
- The ‘customized’ document is located in My Documents
- The databases are located in c:\ProgramData\<manufacturer>\<appname>\<databasename>.sdf

This is bad … In this particular scenario you get the ‘unable to load sqlceme35.dll’.

It is OK when:

- The SQL Compact Dll’s are also located in c:\Windows\System32 (privately deployed, no option however)

- The ‘customized’ document is located in c:\Program Files\<manufacturer>\<appname>\ , next to the privately deployed SQLCe and customization assembly (also no option due to protected area)

Now for the solution:

I found out that when using the ‘normal’ installation routine it is likely your customization will be installed into the ClickOnce cache. That means that your customization and the privately deployed SQLCE dlls from that time on are in a located in a different location making it impossible to find when loading. That brings us to the option to ‘attach’ the customization to the absolute location where the assembly customization and the SQLCe assemblies reside. So if you look at the _AssemblyLocation document property of the document you will find that the location is specified as file://c:\... etc etc.

(In a different context this is explained  in http://msdn.microsoft.com/en-us/library/cc616991.aspx under the section of Additional Requirements for Document-level Solutions)

This will solve your ‘unable to load sqlceme35.dll’ error as from now on the customization dll and the privately installed dlls are in the same location. The downside is however that the ClickOnce mechanism is not available in that scenario. On the other hand, if you need the ClickOnce you are able to add SQL Compact as prerequisite and add the bootstrapper package to the installer.