CODEWITHSUNDEEP
sqlvisual-studio-2010wix
Mitchell Carroll
Mitchell Carroll

Reputation: 11

How to create an SQL Server database using Wix

I am trying to create a .msi that will install a SQL Server Database. I have the following code but when I run the .msi on a virtual machine, the Database does not show up in the SQL Server Management Studio on the VM. What am I missing?

<Directory Id="TARGETDIR" Name="SourceDir" ComponentGuidGenerationSeed="MYGUID-AEAC-4fdf-86D0-05B33BAD4550">      
     <Component Id="SqlScripts" Guid="MyGUID-A87F-42D2-B6E7-BD8790548625">

        <util:User Id="SQLUser" Name="User" Password="password" CreateUser="yes" UpdateIfExists="no"/>

           <sql:SqlDatabase Id="SQLDatabase" Database="MyDatabase" User="SQLUser" Server=".\SQLEXPRESS"
         CreateOnInstall="no" CreateOnUninstall="no" CreateOnReinstall="no" ConfirmOverwrite="no"
         DropOnInstall="no" DropOnReinstall="no" DropOnUninstall="no" ContinueOnError="yes">
           <sql:SqlScript Id="MyDatabase" BinaryKey="MyDatabase" ExecuteOnInstall="yes" ExecuteOnReinstall="no" ExecuteOnUninstall="no" ContinueOnError="no" Sequence="4"/>
        </sql:SqlDatabase>

        <!-- stops and restarts World Wide Web Publishing Service (W3SVC) during the install or uninstall -->
        <ServiceControl Id="ServiceControlWebSite" Name="W3SVC" Start="both" Stop="both" Wait="yes" />
     </Component>
  </Directory>

  <Binary Id="MyDatabase" SourceFile="myDatabase.sql"/>

  <Feature Id="Complete" Title="Setup" Level="1">         
     <ComponentRef Id="SqlScripts" />
  </Feature>

I don't know what the Service Control is, but I don't think it's of any concern.

Is Wix supposed to create the database or should the script do that? Currently the script creates the tables and inserts data. I've also tried with the script creating the database but nothing happens. I'm doubting that any of this code is executed because nothing is created.

When I run the scripts individually in SQL Server Management Studio the database is created, which further increases my doubt that the Wix code is working.

Upvotes: 0

Views: 1366

Answers (1)

caveman_dick
caveman_dick

Reputation: 6667

I always set CreateOnInstall="yes" as it will then create the database for you if it doesn't exist or give you a refernce to it if it does. Also set ContinueOnError="no" so that any errors are shown to the user.

The biggest potential issue is the user that you are using to run the script. It is being created and unless you give it admin rights (which be the looks of it you aren't) it won't have the required permissions. I run my sql scripts as sa and then create a limited access user for the app to use.

As for the service control, the comment above tells you what it is doing! ;)

Upvotes: 1

Related Questions