[wix-users] Patch for DB(SqlScript) with no Uninstall option available

Mon Jun 11 08:58:14 PDT 2018

I had a small finding (from the link below). If the KeyPath attribute is at
the Component level and not at the file level and if we add such a new
Component in the new MSI for patch, then the Uninstall option gets
disappeared. So, for regular files, if we put the KeyPath attribute at file
level rather than at the Component level, then Uninstall option is
available. But, for SqlScripts, since there is no KeyPath attribute, these
options are ruled out. I am still struggling with the Uninstall option
getting disappeared after Patch installation in case of SqlScript with a
new component added in the new msi for patch.

https://stackoverflow.com/questions/29752633/how-to-correctly-add-new-files-in-wix-installation-to-create-an-uninstallable-pa

On Tue, Jun 5, 2018 at 10:13 PM, Ven H <venh.123 at gmail.com> wrote:

> As asked by Edwin, I am providing the description for my 2nd problem,
> where the Uninstall option is not available. Sorry for the long email, but
> I wanted to be as descriptive as possible.
>
> Basically, I have a Visual Studio MSI project with a Sample.txt and
> CREATE_TABLE.sql files at the root level. The Sample.txt has a text "This
> is version 1.0.0." and nothing else and the CREATE_TABLE.sql has an SQL
> statement to create a table as below.
>
> CREATE_TABLE.sql
>
> use TestDB
> go
>
> if not exists( select 1 from information_schema.tables where table_name =
> 'EMPLOYEES' )
> begin
> create table dbo.EMPLOYEES
> (
> EMP_ID INT NOT NULL,
> EMP_NAME NVARCHAR(100) NOT NULL
> )
> end
> go
>
>
> Product.wxs for v1.0.0
>
> <?xml version="1.0" encoding="UTF-8"?>
> <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 Id="F3374DBE-A524-44FA-9052-4C6F88249F10" Name="DBPatchSample"
> Language="1033" Version="1.0.0.0" Manufacturer="DB_Patch_Sample"
> UpgradeCode="42855AC1-CE52-4366-B419-83503155C417">
> <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine"
> />
>
> <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is
> already installed." />
> <MediaTemplate EmbedCab="yes" />
>
>     <util:User Id="SQLUser" Name="[SQLUSER]" Password="[SQLPASSWORD]" />
>     <Binary Id="binCreateTbl" SourceFile="CREATE_TABLE.sql" />
>
>     <Property Id="MSIUSEREALADMINDETECTION" Value="1" />
>     <Property Id="MSIFASTINSTALL" Value="1"/>
>     <Property Id="MsiLogging" Value="v" />
>     <Property Id="MSIENFORCEUPGRADECOMPONENTRULES" Value="1" />
>     <Property Id="SQLSERVER" Value="SqlServerName"/>
>     <Property Id="SQLINSTANCE" Value="SqlInstanceName"/>
>     <Property Id="SQLDB" Value="TestDB"/>
>     <Property Id="SQLUSER" Value="sa"/>
>     <Property Id="SQLPASSWORD" Value="Sa_Password"/>
>
> <Feature Id="ProductFeature" Title="DBPatchSample" Level="1">
> <ComponentGroupRef Id="DbCG" />
> </Feature>
>   <UI Id="MyWixUI_Mondo">
>       <UIRef Id="WixUI_Mondo" />
>     </UI>
>     <UIRef Id="WixUI_ErrorProgressText" />
> </Product>
>
> <Fragment>
> <Directory Id="TARGETDIR" Name="SourceDir">
> <Directory Id="ProgramFilesFolder">
> <Directory Id="INSTALLFOLDER" Name="DBPatchSample" />
> </Directory>
> </Directory>
> </Fragment>
>
> <Fragment>
> <ComponentGroup Id="DbCG">
>       <Component Id="fileComp" Guid="92526A26-9404-449B-9EAA-7FC175F2EBE4"
> Directory="INSTALLFOLDER" KeyPath="yes">
>         <File Id="sampleTxt" Name="Sample.txt" Source="Sample.txt" />
>       </Component>
>       <Component Id="dbComp1" Guid="B4E70B72-DB9C-4215-B4DF-022B56A26C01"
> Directory="INSTALLFOLDER" KeyPath="yes">
>         <sql:SqlDatabase Id="msiTestDB1" Database="[SQLDB]"
> Server="[SQLSERVER]" Instance="[SQLINSTANCE]" CreateOnInstall="yes"
> User="SQLUser" />
>         <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl"
> ExecuteOnInstall="yes"  ContinueOnError="no" SqlDb="msiTestDB1"
> Sequence="1"  />
>       </Component>
> </ComponentGroup>
> </Fragment>
> </Wix>
>
> With this, I created an MSI and installed it. It does create the table
> with the two columns in it. In the C:\Program Files(x86)\DBPatchSample
> folder, I could find only the Sample.txt file and not the CREATE_TABLE.sql.
> This is probably because the sql file is a binary and has been set to
> execute on install.
>
> In my bin/debug folder, I created 3 folders namely 1.0.0, 1.0.1 and Patch.
> Within 1.0.0 folder and 1.0.1 folders, I created a folder called "admin".
> In the Patch folder, I copied my Patch.wxs, whose markup is as given below.
> Then I moved the first MSI to 1.0.0 folder.
>
> Patch.wxs
>
> <?xml version="1.0" encoding="UTF-8"?><Wix xmlns="http://schemas.microsof
> t.com/wix/2006/wi">
>   <PatchCreation WholeFilesOnly="yes" CleanWorkingFolder="yes"
> OutputPath="patch.pcp" Id="224C316C-5894-4771-BABF-21A3AC1F75FF">
>     <PatchInformation Description="Patch Info Description" Comments="Patch
> Info Comments" Manufacturer="DB_Patch_Sample" Languages="1033"
> ShortNames="no" Compressed="yes"/>
>     <PatchMetadata Description="Patch Metadata Description"
> ManufacturerName="DB_Patch_Sample" MoreInfoURL="URL_Here"
> AllowRemoval="yes" Classification="Update" DisplayName="DB Patch Sample
> Patch" TargetProductName="DBPatchSample"/>
>     <Family MediaSrcProp="Sample" SequenceStart="5000" DiskId="5000"
> Name="Sample">
>       <UpgradeImage SourceFile="D:\MyProjects\WIX\
> DBPatchSample\bin\Debug\1.0.1\admin\DBPatchSample.msi" Id="SampleUpgrade">
>         <TargetImage Order="2" IgnoreMissingFiles="no"
> SourceFile="D:\MyProjects\WIX\DBPatchSample\bin\Debug\1.0.0\admin\DBPatchSample.msi"
> Id="SampleTarget"/>
>       </UpgradeImage>
>     </Family>
>     <PatchSequence Supersede="yes" PatchFamily="SamplePatchFamily"
> Sequence="1.0.0.0"/>
>   </PatchCreation>
> </Wix>
>
> Now, to create the 1.0.1, I added a new file called CREATE_TABLE_1
>
> CREATE_TABLE_1.sql
>
> use TestDB
> go
>
> if not exists( select 1 from information_schema.tables where table_name =
> 'EMPLOYEES1' )
> begin
> create table dbo.EMPLOYEES1
> (
> EMP_ID INT NOT NULL,
> EMP_NAME NVARCHAR(100) NOT NULL
> )
> end
> go
>
> Product.wxs for v1.0.1
>
> <?xml version="1.0" encoding="UTF-8"?>
> <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 Id="F3374DBE-A524-44FA-9052-4C6F88249F10" Name="DBPatchSample"
> Language="1033" Version="1.0.1.0" Manufacturer="DB_Patch_Sample"
> UpgradeCode="42855AC1-CE52-4366-B419-83503155C417">
> <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine"
> />
>
> <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is
> already installed." />
> <MediaTemplate EmbedCab="yes" />
>
>     <util:User Id="SQLUser" Name="[SQLUSER]" Password="[SQLPASSWORD]" />
>     <Binary Id="binCreateTbl" SourceFile="CREATE_TABLE.sql" />
>     <Binary Id="binCreateTbl1" SourceFile="CREATE_TABLE_1.sql" />
>
>     <Property Id="MSIUSEREALADMINDETECTION" Value="1" />
>     <Property Id="MSIFASTINSTALL" Value="1"/>
>     <Property Id="MsiLogging" Value="v" />
>     <Property Id="MSIENFORCEUPGRADECOMPONENTRULES" Value="1" />
>     <Property Id="SQLSERVER" Value="SqlServerName"/>
>     <Property Id="SQLINSTANCE" Value="SqlInstanceName"/>
>     <Property Id="SQLDB" Value="TestDB"/>
>     <Property Id="SQLUSER" Value="sa"/>
>     <Property Id="SQLPASSWORD" Value="Sa_Password"/>
>
> <Feature Id="ProductFeature" Title="DBPatchSample" Level="1">
> <ComponentGroupRef Id="DbCG" />
> </Feature>
>   <UI Id="MyWixUI_Mondo">
>       <UIRef Id="WixUI_Mondo" />
>     </UI>
>     <UIRef Id="WixUI_ErrorProgressText" />
> </Product>
>
> <Fragment>
> <Directory Id="TARGETDIR" Name="SourceDir">
> <Directory Id="ProgramFilesFolder">
> <Directory Id="INSTALLFOLDER" Name="DBPatchSample" />
> </Directory>
> </Directory>
> </Fragment>
>
> <Fragment>
> <ComponentGroup Id="DbCG">
>       <Component Id="fileComp" Guid="92526A26-9404-449B-9EAA-7FC175F2EBE4"
> Directory="INSTALLFOLDER" KeyPath="yes">
>         <File Id="sampleTxt" Name="Sample.txt" Source="Sample.txt" />
>       </Component>
>       <Component Id="dbComp1" Guid="B4E70B72-DB9C-4215-B4DF-022B56A26C01"
> Directory="INSTALLFOLDER" KeyPath="yes">
>         <sql:SqlDatabase Id="msiTestDB1" Database="[SQLDB]"
> Server="[SQLSERVER]" Instance="[SQLINSTANCE]" CreateOnInstall="yes"
> User="SQLUser" />
>         <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl"
> ExecuteOnInstall="yes" ContinueOnError="no" SqlDb="msiTestDB1"
> Sequence="1"  />
>       </Component>
>       <Component Id="dbComp2" Guid="968CEE51-8A35-47F1-89C4-38C7C159B831"
> Directory="INSTALLFOLDER" KeyPath="yes">
>         <sql:SqlScript BinaryKey="binCreateTbl1" Id="script_CreateTbl1"
> ExecuteOnInstall="yes" ContinueOnError="no" SqlDb="msiTestDB1" Sequence="2"
> />
>       </Component>
> </ComponentGroup>
> </Fragment>
> </Wix>
>
> I rebuilt this MSI and moved it inside 1.0.1 folder.
>
> Then I ran the following commands to create the Patch in a command prompt
> after changing to the appropriate directory (I am using PCP approach for
> Patch).
>
> msiexec.exe /a 1.0.0\DBPatchSample.msi /qb TARGETDIR=D:\MyProjects\WIX\DB
> PatchSample\bin\Debug\1.0.0\admin
>
> msiexec.exe /a 1.0.1\DBPatchSample.msi /qb TARGETDIR=D:\MyProjects\WIX\DB
> PatchSample\bin\Debug\1.0.1\admin
>
> candle.exe Patch\patch.wxs
>
> light.exe patch.wixobj -out patch\patch.pcp
>
> msimsp.exe -s patch\patch.pcp -p patch\patch.msp -l patch.log
>
> After I installed the Patch, the Uninstall option is not available for
> this Patch in the Installed Updates in the ARP.
>
> Can anyone please help me with this?
>