[wix-users] Error in SqlString

Sun Apr 29 10:23:31 PDT 2018

Thanks a lot, Edwin. Please find the code for my sample below

<?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="978986F3-6999-417B-8A5F-20282AC99A4C" Name="CreateDB"
Language="1033" Version="1.0.0.0" Manufacturer="DBCompany"
UpgradeCode="e88e641a-b333-4338-93ef-4336829bd51d">
<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]" />
    <Property Id="MSIUSEREALADMINDETECTION" Value="1" />
    <Property Id="MSIFASTINSTALL" Value="1"/>
    <Property Id="MsiLogging" Value="v" />
    <Property Id="MSIENFORCEUPGRADECOMPONENTRULES" Value="1" />
    <Property Id="SQLSERVER" Value="ServerName"/>
    <Property Id="SQLDB" Value="msdb"/>
    <Property Id="SQLUSER" Value="sa"/>
    <Property Id="SQLPASSWORD" Value="saPwd"/>
<Feature Id="ProductFeature" Title="CreateDB" Level="1">
<ComponentGroupRef Id="ProductComponents" />
</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="CreateDB" />
</Directory>
</Directory>
</Fragment>

<Fragment>
<ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER">
<Component Id="ProductComponent"
Guid="966A3533-FD52-4136-A5D2-B47C561E4D82" KeyPath="yes">
         <sql:SqlDatabase Id="msiTestDB" Database="[SQLDB]"
Server="[SQLSERVER]" CreateOnInstall="yes" User="SQLUser" />
<sql:SqlString Id="strTestDB" ExecuteOnInstall="yes" ContinueOnError="no"
User="SQLUser"  SqlDb="msiTestDB"
        SQL="USE master
        if db_id('TestDB') is null
        CREATE DATABASE TestDB
        CONTAINMENT = NONE
        ON  PRIMARY
( NAME = N'Test_Data', FILENAME = N'[\[]INSTALLFOLDER[\]]\Test_data.mdf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
       LOG ON
( NAME = N'Test_Log', FILENAME = N'[\[]INSTALLFOLDER[\]]\Test_log.ldf' ,
SIZE = 10MB , MAXSIZE = 20GB , FILEGROWTH = 1MB )" />
</Component>
</ComponentGroup>
</Fragment>
</Wix>

Regards,
Venkatesh

On Sun, Apr 29, 2018 at 9:42 PM, Edwin Castro <egcastr at gmail.com> wrote:

> I see a bunch of * characters on each line. I assume that is an email
> artifact and not really in your sqlstring.
>
> Can you shows us your WiX authoring so we can see how you define the sql
> text, how you evaluate [INSTALLDIR], and how you define the actual
> sqlstring element? I expect the problem lies in how you're doing those
> things rather than in the unmidified sql text you are trying to run.
>
> As an aside, SqlString and friends do *not* support the GO statement so
> your sql text will get modified if you have it in your string. Perhaps this
> modification results in sql text that you don't expect. I'd have to go look
> at the code to get the specifics... the code is on github.
>
> All that said, why are you trying to create the database with SqlString
> when SqlDatabase will already create the database for you?
>
> I thought you were going to specify the secondary data file directly in
> that CREATE DATABASE but you didn't. I would let wix create the database
> first and then use SqlString to alter the database to add the secondary
> data files.
>
> I expect you have a problem evaluating [INSTALLDIR] so you'll probably
> still have the same problem in your alter sql text. Seeing the actual wix
> code will help figure out what is wrong.
>
> --
> Edwin G. Castro
>
>
> On Sun, Apr 29, 2018, 02:46 Ven H via wix-users <
> wix-users at lists.wixtoolset.org> wrote:
>
>> One small correction. In the sql above, I forgot to mention the path.
>>
>> *USE [master]*
>> *GO*
>>
>> */****** Object:  Database [myDB]    Script Date: DateAndTime******/*
>> *if db_id(' myDB ') is null*
>> * CREATE DATABASE [ myDB ]*
>> *  CONTAINMENT = NONE*
>> *  ON  PRIMARY *
>> * ( NAME = N' myDB _Data', FILENAME = N' [INSTALLDIR]\myDB _data.mdf' ,
>> SIZE = 1024MB , MAXSIZE = UNLIMITED, FILEGROWTH = 200MB )*
>> *  LOG ON *
>> * ( NAME = N' myDB _Log', FILENAME = N'  [INSTALLDIR]\myDB _log.ldf' ,
>> SIZE
>> = 1024MB , MAXSIZE = 2048GB , FILEGROWTH = 100MB )*
>> *GO*
>>
>>
>> Regards,
>> Venkatesh
>>
>> On Sun, Apr 29, 2018 at 3:08 PM, Ven H <venh.123 at gmail.com> wrote:
>>
>> > I have the following sql which I am trying to execute using an SqlString
>> > element. I am using SqlString and not SqlScript, because I have to pass
>> > some parameters for the data file and log file paths. I cannot use
>> > SqlFileSpec also because I will have multiple data files also.
>> >
>> > *USE [master]*
>> > *GO*
>> >
>> > */****** Object:  Database [myDB]    Script Date: DateAndTime******/*
>> > *if db_id(' myDB ') is null*
>> > * CREATE DATABASE [ myDB ]*
>> > * CONTAINMENT = NONE*
>> > * ON  PRIMARY *
>> > * ( NAME = N' myDB _Data', FILENAME = N' myDB _data.mdf' , SIZE =
>> 1024MB ,
>> > MAXSIZE = UNLIMITED, FILEGROWTH = 200MB )*
>> > * LOG ON *
>> > * ( NAME = N' myDB _Log', FILENAME = N' myDB _log.ldf' , SIZE = 1024MB ,
>> > MAXSIZE = 2048GB , FILEGROWTH = 100MB )*
>> > *GO*
>> >
>> > I am not able to get it working. Initially, it complained about the GO
>> > keyword, but even if I remove, it keeps saying incorrect syntax near =.
>> I
>> > tried enclosing the values within single quotes, but this also didn't
>> work.
>> > Can anyone please help how to get this working. Also, I need to read the
>> > above from a file and dynamically assign it to the SQL property of the
>> > SqlString element. Please help.
>> >
>>
>> ____________________________________________________________________
>> WiX Toolset Users Mailing List provided by FireGiant
>> http://www.firegiant.com/
>>
>