When publishing a ClickOnce application, you can open the Prerequisites dialog and select any of the packages to be installed prior to the ClickOnce application. You would probably select the .NET Framework that your application targets and Windows Installer 3.1. You would also select the SQLServer Express 2008 database software if you are using a SQLExpress database in your application.
Last summer, Microsoft released SP-1 for SQLServer 2008 and several people posted to the MSDN ClickOnce Forum and Stack Overflow asking where they could get a new, updated bootstrapper package. I decided to pursue it and see if I could track it down.
I’ll just ask Microsoft.
Saurabh Bhatia, who’s kind enough to help me answer the most difficult ClickOnce questions, routed me to the SQLServer Express program manager, Krzysztof Kozielczyk. Krzysztof said he totally agreed that Microsoft should release a new version of the bootstrapper package every time they updated SQLExpress, but they were busy working on the upcoming releases and he did not have the resources to work on it. It’s hard to know what to say when you tell someone “You should have this” and they say “You’re right, we should, I’m sorry we don’t.” (I’m going to have to remember to use that on my boss in the future.)
According to Krzysztof, the problem is that they can’t just create the package and release it. They have to create the boostrapper package and test it in a bunch of combinations or different variables, such as operating system version, SQLServer Express version (to test that the upgrade works), .NET version, number of olives in a jar, and kinds of mayonnaise available on the west coast, among others. Then if it passes mustard (haha), they have to find somewhere to post it, make it available, and then keep it updated. So more people are involved than just his team, and at that time, everyone in DevDiv at Microsoft was working on the upcoming release of VS2010.
Persistence is one of my best qualiities, so I continued over the following months to
badger try to convince Krzysztof to at least help me create the package and provide it to the community, especially after R2 came out. Every time someone posted the request to one of the forums, I would take a screenshot and send it to him. He was unfailingly kind, but stuck to his guns. There’s even been a bug filed in Connect for this issue. (On the bright side, Krzysztof did answer the occasional SQLExpress question from the forums for me.)
Success! (and disclaimers)
Well, we’ve had quite a bit of back and forth lately, and I’m happy to report that I now have a bootstrapper package that I can use to install SQLServer Express 2008 R2 as a prerequisite to a ClickOnce application. (I skipped 2008 SP-1). Krzysztof did not provide the solution, but by peppering the guy with questions, I have finally created a working package. So big thanks to Krzysztof for continuing to answer my questions and put up with the badgering over the past few months. Now he doesn’t need to avoid me when I go to the MVP Summit at the end of this month!
Disclaimer: This did not come from Microsoft, I figured it out and am posting it for the benefit of the community. Microsoft has no responsibility or liability for this information. I tested it (more on that below), but you must try it out and make sure it works for you rather than assuming. Legally, I have to say this: Caveat emptor. There is no warranty expressed or implied. Habeas corpus. E pluribus unum. Quid pro quo. Vene vidi vici. Ad infinitum. Cogito ergo sum. That is all of the Latin-ish legalese I can come up with to cover my you-know-what. (I have more sympathy for Krzysztof now.)
I tested the package as the prerequisite for a ClickOnce application with both 32-bit and 64-bit Windows 7. I tested it with no SQLExpress installed (i.e. new installation) and with SQLExpress 2008 installed (to test that it updates it to R2). I did not test it with a setup & deployment package, but I’m certain it will work. Maybe one of you will try it out and let me know. I did not try it all of the combinations listed above for Microsoft testing, although I did eat some olives out of a jar while I was testing it.
Enough talk, show me the goods
Here are the instructions on creating your own SQLServer 2008 R2 prerequisite package. You must have a SQLServer 2008 Prerequisite package in order to do this. If you don’t have one, and you don’t have VS2010, install the Express version (it’s free). I think you can also install the Windows 7 SDK and it ill provide those prerequisite packages (I’m guessing, since they show up under the Windows folder under SDKs).
I didn’t post the whole package because there is a program included in the bootstrapper package called SqlExpressChk.exe that you need, and it is not legal for me to distribute it. (I don’t think my Latin would withstand the scrutiny of a Microsoft lawyer.)
First, locate your current bootstrapper packages. On my 64-bit Windows 7 machine, they are under C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages. If you don’t have the \en\ folder, presumably you have one for a differeng language; just wing it and substitute that folder in the following instructions.
- Copy the folder called “SQLExpress2008” to a new folder called “SQLExpress2008R2”.
- Download the zip file with the product.xml and package.xml you need from here and unzip it.
- In the new \SQLExpress2008R2\ folder, replace the product.xml file with my version.
- In the \SQLExpress2008R2\en\ folder, replace the package.xml file with my version.
- Delete the two SQLExpr*.exe files in the \SQLExpress2008R2\en\ folder.
- Download the 32-bit install package from here and put it in the \SQLExpress2008R2\en\ folder.
- Download the 64-bit install package from here and put it in the \SQLExpress2008R2\en\ folder.
- Close and re-open Visual Studio. You should be able to go to the Publish tab of your application and click on the Prerequisites button and see “SQLExpress2008R2” in the list.
Do NOT remove the SqlExpressChk.exe file from the \SQLExpress2008R2\ folder, or the eula.rtf from the \SQLExpress2008R2\en\ folder.
If you’re using ClickOnce deployment, don’t forget that it does not install updates to the prerequisites automatically – it only updates the ClickOnce application. You will either have to have your customers install the prerequisite before they upgrade the ClickOnce application (just ask them to run the setup.exe again), or programmatically uninstall and reinstall the application for them, including the new prerequisite. (The code for that is in this MSDN article.)