How to run sql script to create database using Wix

I'm running into problem creating database with Wix. The examples(see link below) i can find are all creating database first using wix and running a create table sql script. The problem 开发者_如何学Cis that i need to set the wix to run sql script with CREATE DATABASE to create the database and tables, and NOT to create the database first using the SqlDatabase tag in .wxs file. Is this possible to do this in Wix? I'm using SQL Server Express. Any example on how to do this would be helpful.

http://www.rrreese.com/Article/Show/WiX%20SQL

Example of sql script:

CREATE DATABASE My_DB
CREATE TABLE Test (Value1 CHAR(50), Value2 INTEGER)
CREATE INDEX TestIndex ON Test (Value1)

Thanks in advance. Any help is much appreciated.

Why don't you have a script file containing SQL code to create the database.

<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" 
xmlns:util="http://schemas.microsoft.com/wix/UtilExtension" 
xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension">
  <Product Name='SQL app 1.0' Id='DB501C18-86C7-4D14-AEC0-86416A69ABDE' Language='1033' Codepage='1252'
     Version='1.0.0' Manufacturer='ABC Ltd.'>
            <Package Id='????????-????-????-????-????????????' Keywords='Installer' Description="SQL App 1.0 Installer"
              Comments='Comment.' Manufacturer='ABC Ltd.' InstallerVersion='100'
              Languages='1033' Compressed='yes' SummaryCodepage='1252' />
        <Media Id='1' Cabinet='Sample.cab' EmbedCab='yes' />
        <User Id="MySQLUser" Name="[SQLUSER]" Password="[SQLUSERPASSWORD]"></User>
        <Directory Id='TARGETDIR' Name='SourceDir'>
            <Directory Id='ProgramFilesFolder' Name='PFiles'>
                <Directory Id='INSTALLDIR' Name='TestSQL'>
                      <Component Id="MySqlComponent" Guid="C50999A0-02FD-42d5-9F65-7375318DD328">
                        <SqlDatabase Id="MySqlDatabase" Database="MyDatabase" Server="[SQLSERVER]" Instance="[SQLINSTANCE]"
                            CreateOnInstall="yes" DropOnUninstall="yes" User="MySQLUser" ContinueOnError="yes">
                              <SqlScript Id="CreateDatabase" ExecuteOnInstall="yes" BinaryKey="CreateTablesBin"></SqlScript>
                        </SqlDatabase>
                      </Component>
                </Directory>
             </Directory>
         </Directory>
        <Binary Id="CreateTablesBin" src="CreateDatabase.sql"></Binary>
        <Feature Id='Complete' Level='1' Description="Full" Title="Full Installation">
            <ComponentRef Id='MySqlComponent' />
        </Feature>
    </Product>
</Wix>

You need to have the SqlDatabase tag. Without it, the installer will never know on which server and database the scripts have to run. However, you can set both CreateOnInstall and DropOnUninstall (there are three CreateOnX and three DropOnX, you might want to check them all) to no to prevent the database from being created or dropped automatically.

Note that if you do this, you should provide a SqlScript that runs on rollback, to drop the database in case of an installer failure. This is usually done automatically with the SqlDatabase tag.