Configuring the Package.xml for SQL Express ClickOnce Pre-Requisite install ?

Question

I have a winform app being installed by ClickOnce and I need to be able to enable Remote Connections in sqlexpress by default.

Also, I need to make sure the local user has access to the SQL instance. How do I edit the manifest file?

My app is a part of a Merge Replication topology. I inherited the DAL and until I change it I cannot switch to SQL CE due to SProc's limitation's.

SQL Express is ClickOnce (able) and is a default in VS2008 as a prerequisite.

Below is the Product.xml of the SQL Express Bootstrapper package. How can I modify this to accomplish what I need? Has anyone else had to modify this? Here is an example of this.

<Command PackageFile="sqlexpr32.exe" Arguments="-q /norebootchk /qn reboot=ReallySuppress addlocal=all instancename=SQLEXPRESS SQLAUTOSTART=1 ADDUSERASADMIN=1" EstimatedInstalledBytes="225000000" EstimatedInstallSeconds="420">
<InstallConditions>
 <BypassIf Property="SQLExpressInstalled" Compare="ValueEqualTo" Value="0"/>
 <BypassIf Property="VersionNT" Compare="VersionLessThan" Value="5.1"/>
 <FailIf Property="AdminUser" Compare="ValueEqualTo" Value="false" String="AdminRequired"/>
 <FailIf Property="Version9x" Compare="ValueExists" String="InvalidPlatformXP"/>
 <FailIf Property="VersionNT" Compare="VersionLessThan" Value="5.1.2" String="InvalidPlatformXP"/>
 <FailIf Property="ProcessorArchitecture" Compare="ValueNotEqualTo" Value="Intel" String="InvalidPlatformArchitecture"/>
 </InstallConditions>
<ExitCodes>
 <ExitCode Value="0" Result="Success"/>
 <ExitCode Value="1641" Result="SuccessReboot"/>
 <ExitCode Value="3010" Result="SuccessReboot"/>
 <ExitCode Value="50037" Result="Fail" String="MissingMSXml"/>
 <ExitCode Value="50251" Result="Fail" String="MissingMSXml"/>
 <ExitCode Value="50198" Result="Fail" String="InsufficientHardware"/>
 <ExitCode Value="50236" Result="Fail" String="InsufficientHardware"/>
 <ExitCode Value="50222" Result="Fail" String="InvalidPlatformOSServicePacks"/>
 <ExitCode Value="70003" Result="Fail" String="InvalidPlatformOSServicePacks"/>
 <ExitCode Value="50247" Result="Fail" String="InvalidPlatformIE"/>
 <ExitCode Value="50248" Result="Fail" String="InvalidPlatformIE"/>
 <ExitCode Value="70004" Result="Fail" String="AnotherInstanceRunning"/>
 <ExitCode Value="70032" Result="Fail" String="BetaComponentsFailure"/>
 <ExitCode Value="70033" Result="Fail" String="InvalidPlatformArchitecture"/>
 <DefaultExitCode Result="Fail" FormatMessageFromSystem="true" String="GeneralFailure"/>
 </ExitCodes>
 </Command>

I thank everyone for there time.

Answer 1

SQL Express Edition is still a server class database. It runs all the time as a service, and like any server-class database likes to a lot of resources. It's just not really a good choice for local desktop app.

If you need a single user database use something like SQL Server Compact Edition, Sqlite, or even Access. I doubt you'd appreciate it if you installed a desktop app only to find it dragged a full sql server install along with it.

Answer 2

SQL Server is a service. It seems it is theoretically possible to install a service with click once, see here and here.

I suspect that installing SQL Express by click once is impossible because it is a technology for deploying sandboxed applications. SQL server runs close to the metal and wouldn't likely work in a sandbox.

SQL Compact is another story and is a more appropriate way to deploy a click once app with its database.

Or let the app reference the database on another server.

EDIT: And if I've gotten your question all wrong (and this is a click once app on a client workstation that already has SQL Express), you'll need to enable remote connections this way. There are scraps of evidence that the SAC tool can be used to do it programmatically.