I'm working on a VB.Net 2005 application that uses SQLCE as a backend database.
Following the instructions on how to do a private installation (http://msdn2.microsoft.com/en-us/library/bb219482.aspx) so that admin rights were not needed, I successfully got it working as a ClickOnce deployment. But today during a demo for a client, they ran into this error while installing the latest version from our publishing URL:
"Unable to install or run the application. Application requires that assembly System.Data.SQLServerCe version 9.0.42 .... must be installed in the Global Assembly Cache."
Basically it's saying the user isn't an admin so they can't install. This is the problem I went to great lengths to try to avoid, and is the reason we are using SQL CE in the first place: admin rights are not required to install our application's database. To our knowledge, SQL CE is the only Microsoft product that fits this scenario.
Now, I haven't changed anything in the publishing of the application to my knowledge, and when I check the project prerequisites, the SQLCE engine still isn't in there (as summarized at the above URL). Again following the instructions at the URL above for private installation, the required assemblies are still part of the project's files.
It must be something I did to cause this, but I have no memory of changing anything in regards to this part of the application. Our deadline is in 4 days and I cannot continue development until I get past this issue.
Where do I look to fix this problem? A little help would be greatly appreciated.
It is currently unknown as to what caused this problem and the fix may be a kludge. However, we have made some changes after much agonizing, and the application now installs with a newly-created standard user account on Windows XP. After seeing what we did to fix this issue below, any further comments are still appreciated, since we're working with a db system that's only a few months old, and which is not documented with the same rigor as most Microsoft products.
Upon checking the Prerequisites for our application (at Project Properties | Publish | Prerequisites), we saw that not only was SQL CE not selected as a prerequisite, it was not even listed as something we could deselect. Yet, it was still being configured to be installed into the GAC by the ClickOnce engine. Earlier in the development process, perhaps three weeks ago, it did appear in the prerequisites list, and was explicitly deselected. But now it had... disappeared.
However, upon checking what would be installed at Project Properties | Publish | Application Files, we saw that indeed, System.Data.SQLServerCE.dll was being included in the install, and its Publish Status was set as Prerequisite(Auto). Certainly we did not explicitly make this setting, and we are still unable to explain how it happened. Add to this that it could not be deselected on the Prerequisites tab, and you begin to understand the confusion surrounding this issue. (NOTE: we found this same problem with the Microsoft Visual Studio Report Viewer.)
Our first cut at a fix did not work: we changed the Publish status on each item listed as Prerequisite(Auto) to Exclude. The deployment completed without error, but after installation the application could not find required code to execute correctly, and threw unhandled errors at runtime.
Our second try at a fix is where we are now: we changed all items (except Framework 2.0) formerly listed as Prerequisite(Auto) to Include. Interestingly, those items that would not install as prerequisites without Administrator permissions, would now install without error. The last error to be cleared concerned the data file itself.
During our application deployment, we wanted to copy the .sdf database to the program folder. We reference it in code without a fully qualified path, due to the expectation that the database file will be in the same directory as the application executable. However, after clearing the errors described above, we had one last problem to fix: the database file was not found after installation. Checking, we saw that the ClickOnce engine automatically copies the database file to a different folder than the rest of the application files. So, we returned yet again to the Application Files list at Project Properties | Publish, and found that something Auto was happening with our database as well: its Publish Status was listed as Data File(Auto). Taking yet another wild guess, we changed it to Include. Lo, behold and voila, everything now works.
The very unfortunate issues here are 1) that we don't know why it didn't work in the first place, 2) we don't know what happened to change what we thought were correct settings for publication, and 3) we don't know whay what we did to fix the problems worked. To wit, nothing makes logical sense at this point.
We don't know whether any of the things we went through should be classed as bugs in either ClickOnce, Visual Studio, or anything else. Admittedly we are rather over our heads using several technologies for the first time. However, it needs to be said: at best, the documentation for this deployment scenario is sadly lacking (or just plain wrong). At worst, this deployment scenario is not supported by default, and it takes several arcane spells and a lot of wing of bat and eye of newt due to bugs in the system.
At any rate, I hope this tale of woe may help others who may be doing this for the first time.
