Executing sqlcmd from WiX

I have been working on an install pack that needs to run and sql script. The script requires several parameters and the current version of the install (Wise Install Builder) uses the sqlcmd.exe to substitute the values using the -v option. I looked at using the inbuilt WiX custom actions but they don't accept parameters so I decided to run sqlcmd from WiX using a custom action.

I have included a full working sample at the end of this post but the key sections are as follows:

This sections finds the path to the sqlcmd.exe by searching the registry for the path to sql binn folder.

<Property Id="SQLBINDIR">
  <RegistrySearch Id="SqlBinDir" 
    Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup" 
    Name="Path" 
    Type="raw" />
</Property>

This section builds the command line for sqlcmd. In this case the database is the local SQLEXPRESS instance and it uses a trusted connection (-E). The command passes in the parameter "SYSTEM_USER". It writes the output of the command to the log file "Test.log".

<CustomAction Id="sqlcmd.cmd" 
  Property="sqlcmd" 
  Value="&quot;[SQLBINDIR]sqlcmd.exe&quot; -E -S .\SQLEXPRESS -i &quot;[#Test.sql]&quot; -v var=SYSTEM_USER -o [INSTALLDIR]Test.log" />

The final section executes sqlcmd. This will use the current users context to execute the script, that user must have permissions to execute the script. You can also change the attribute CustomAction/@Impersonate to "no" to execute in the context of the local system account (in my case I find this a better option).

<CustomAction Id="sqlcmd" 
  BinaryKey="WixCA" 
  DllEntry="CAQuietExec" 
  Return="check" 
  Execute="deferred" 
  Impersonate="yes" />

I have found this is working quite well and fits in with my method of building SQL scripts for database creation.


Sample

<?xml version="1.0" encoding="UTF-8"?>
<!-- 
    This example executes sqlcmd.
-->

<!-- This is application version number, update for each release -->
<?define Version = "1.0.0" ?>

<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
  <Product Id="*" 
           Name="Execute sqlcmd WiX template" 
           Language="1033" 
           Version="$(var.Version)" 
           Manufacturer="ACME Corporation" 
           UpgradeCode="5D7D77A6-C8A7-4AE3-81C7-884E02AC2D8C">
    <Package Description="Execute sqlcmd WiX template Installation" 
             InstallerVersion="200" 
             Compressed="yes" />

    <Condition Message="You need to be an administrator to install this product.">Privileged</Condition>
    <CustomAction Id="NewerVersion" Error="A later version of [ProductName] is already installed." />

    <Directory Id="TARGETDIR" Name="SourceDir">
      <Directory Id="INSTALLDIR" Name="Test">
        <Component Id="Test.sql" DiskId="1" Guid="E1B7D00A-D6D8-4694-B4E0-E9AF67877119">
          <File Source="Test.sql" KeyPath="yes" />
          <RemoveFile Id ="RemoveTest.log" Name="Test.log" On="uninstall" />
        </Component>
      </Directory>
    </Directory>

    <Feature Id="DefaultFeature" Level="1" ConfigurableDirectory="TARGETDIR">
      <ComponentRef Id="Test.sql" />
    </Feature>

    <Property Id="OLDERVERSIONBEINGUPGRADED" Secure="yes" />

    <Upgrade Id="5D7D77A6-C8A7-4AE3-81C7-884E02AC2D8C">
      <UpgradeVersion Minimum="$(var.Version)" 
                      OnlyDetect="yes" 
                      Property="NEWERVERSIONDETECTED" />
      <UpgradeVersion Minimum="0.0.0" 
                      Maximum="$(var.Version)" 
                      IncludeMinimum="yes" 
                      IncludeMaximum="no" 
                      Property="OLDERVERSIONBEINGUPGRADED" />
    </Upgrade>

    <Media Id="1" Cabinet="contents.cab" EmbedCab="yes" />

    <UIRef Id="WixUI_ErrorProgressText" />

    <InstallExecuteSequence>
      <Custom Action="sqlcmd.cmd" After="InstallFiles">NOT Installed</Custom>
      <!-- Ensure this runs after the CA to set up the property for its cmd line -->
      <Custom Action="sqlcmd" After="sqlcmd.cmd">NOT Installed</Custom>

      <Custom Action="NewerVersion" 
              After="FindRelatedProducts">NEWERVERSIONDETECTED</Custom>
      <RemoveExistingProducts After="InstallInitialize" />
    </InstallExecuteSequence>

    <!-- Find sqlcmd.exe path  -->
    <Property Id="SQLBINDIR">
      <RegistrySearch Id="SqlBinDir" 
                      Root="HKLM" Key="SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup" 
                      Name="Path" 
                      Type="raw" />
    </Property>

    <!-- Need to use "property" CA to get variable substitution -->
    <CustomAction Id="sqlcmd.cmd" 
                  Property="sqlcmd" 
                  Value="&quot;[SQLBINDIR]sqlcmd.exe&quot; -E -S .\SQLEXPRESS -i &quot;[#Test.sql]&quot; -v var=SYSTEM_USER -o [INSTALLDIR]Test.log" />

    <!-- Note that the cmd line and args will come from a property with the same name as the CA, this has been set by the CA above -->
    <CustomAction Id="sqlcmd" 
                  BinaryKey="WixCA" 
                  DllEntry="CAQuietExec" 
                  Return="check" 
                  Execute="deferred" 
                  Impersonate="yes" />
  </Product>
</Wix>

Test.sql contains this:

print convert(varchar(25),GetDate(),121) + ' Executing file: Test.sql'
select $(var);
go