Running SQL Script file through Wix using <sql:SqlScript> element

Question

I am new to Wix Installer. I have a requirement where I have to provide credentials for SQL Server login and run a script from a specific path.

I am not getting what's going wrong.The project is build successfully and .msi is created. After running it I get the following Error:

Error 26204. Error -2147217900: failed to execute SQL string, error detail: Incorrect syntax near '»'., SQL key: CreateUpsizingDatabase SQL string: ï»¿print convert(varchar(25),GetDate(),121) + ' Executing file: SqlTest.sql'

My Sql Script File is as below:

print convert(varchar(25),GetDate(),121) + ' Executing file: SqlTest.sql'

Below is My code:

<?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="*" Name="SqlTest" Language="1033" Version="1.0.0.0" Manufacturer="BLRSCCMCAS01" UpgradeCode="0931a445-07bf-4494-b130-a1f96155021f">
        <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />

        <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
        <MediaTemplate />

        <Feature Id="ProductFeature" Title="SqlTest" Level="1">
            <ComponentGroupRef Id="ProductComponents" />
        </Feature>

    <Binary Id="CreateUpsizingDatabase" SourceFile="C:\Temp\SqlTest.sql" />

    <util:User Id="SQLUser" Name="[User]" Password="[Password]" />

    <sql:SqlDatabase Id="SqlDatabase" Database="[MyDb]" Server="[Server]" User="SQLUser" />

    </Product>

    <Fragment>
        <Directory Id="TARGETDIR" Name="SourceDir">
            <Directory Id="ProgramFilesFolder">
                <Directory Id="INSTALLFOLDER" Name="SqlTest" />
            </Directory>
        </Directory>
    </Fragment>

    <Fragment>
        <ComponentGroup Id="ProductComponents" Directory="INSTALLFOLDER">
      <Component Id="SqlComponent" Guid="15CCE46E-8EA5-42CA-80C5-AC3DB30A9716">
        <sql:SqlScript Id="CreateDatabases" SqlDb="SqlDatabase" ExecuteOnInstall="yes" BinaryKey="CreateUpsizingDatabase" /> 
        <CreateFolder/>
      </Component>
        </ComponentGroup>
    </Fragment>

</Wix>

caveman_dick · Accepted Answer · 2015-02-06 17:27:56Z

7

It looks like you have a UTF8 BOM (ï»¿) in there. Try saving the file as "Unicode (UTF8 Without signature)" using the advanced save options in visual studio.

answered Feb 6, 2015 at 17:27

caveman_dick

6,7073 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SuryaSan Over a year ago

Thanks Man...You showed a well guided direction towards solving it..No Changes it the above code...Just if sql script file is created in Visual studio in my case VS12...it should be saved as with option encoding without signature..if file in sql file is created in SSMS dont need to do anything simple save...

sql chen · Accepted Answer · 2016-12-03 15:31:19Z

0

I got the same error, UTF8 with or without signature both failed. I think the chars in our script string cause the problem (in my case, I included some chinese chars).

I tried to re-generated sql script from sql server, and save this file in ANSI format. It works! And i did some query, the chinese chars are correct in sql server.

an article you might want to refer: http://windows-installer-xml-wix-toolset.687559.n2.nabble.com/Error-executing-sql-scripts-with-WIX-td1488703.html

edited Dec 3, 2016 at 15:31

answered Dec 3, 2016 at 15:25

sql chen

11 bronze badge

Collectives™ on Stack Overflow

Running SQL Script file through Wix using <sql:SqlScript> element

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related