Sunday 24 August 2008

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

23 comments:

Anonymous said...

Hi Neil,

I have tried to execute SQL script with the sample included in the post, I could not compile the source end upwith below compile error:

Unresolved reference to symbol 'Binary:WixCA' in section 'Product:{}'. C:\Wixsample\Fragment.wxi

WixCA is the inbuilt WiX customaction or you have written some custom script in WixCA binary?

Please let me know how can I resolve this error.

Thanks,
Sujan

Neil Sleightholm said...

Are you specifying -ext WixUtilExtension on the light command line?

Anonymous said...

Hi Neil,

I tried your Code it works fine with the minor change, basically if i use thi code:-
< CustomAction Id="sqlcmd.cmd" Property="sqlcmd" Value=""[SQLBINDIR]sqlcmd.exe" -S USDANRMMD1VW021 -i "[#ProductComponent]" -o c:\Test.log -U Build -P build" />

I get this error:

error LGHT0204: ICE03: Not a valid foreign key; Table: CustomAction, Column: Target, Key(s): sqlcmd.cmd

But if i replace this code with the following it works fine.

< CustomAction Id="sqlcmd.cmd1" Property="sqlcmd" Value=""[SQLBINDIR]sqlcmd.exe" -S USDANRMMD1VW021 -i "c:\AddingMaleRow.Sql" -o c:\AddingMaleRow.log -U Build -P build" />

So basically giving exact location of the Sql, could use pls tell me how can i use the file which is part of component as the files wont be installed, just executed.
thanks

Anonymous said...

HI Neil

and if i remove # from [#ProductComponent] it does compile but while executing the sql command it gives this error in log.

Sqlcmd: Error: Internal error at ReadText (Reason: Unspecified error).

Note:- there is only one file in this component.

Neil Sleightholm said...

It is not clear in my sample but [#Test.sql] is the File/@Id, I think your [#ProductComponent] is a component reference.

Anonymous said...

test

Anonymous said...

Hi Neil,

Sample sql works with single test.sql file.

I have a multiple sql files...createDB,createtables,createstoreprocedure,createSCTTrans..etc

How do i run it?

tried merge to one single sql files, its size is huge.

i am looking a way to execute multiple
CustomAction Id="sqlcmd.cmd"....
sqlcmd1.cmd
sqlcmd2.cmd
in sequence.

Any inputs....

Rajesh.

Neil Sleightholm said...

You just need to duplicate the sqlcmd and sqlcmd.cmd custom actions and schedule them one after the other. Having said that I have lots of sql files but find it easier just to string them in to one file.

Anonymous said...

[#Test.sql] is actually the Component/@Id and not the File/@Id.

You can verify this by changing the Component/@Id to TestComponent and the File/@Id to TestFile.

The Candle compiler will complain about the invalid foreign key if you now use the [#TestFile] in the CustomAction, but will be happy with [#TestComponent].

Neil Sleightholm said...

This is not correct [#Test.sql] is the File@/Id. This is the standard way to reference a file.

Anonymous said...

Neal,

I am having problem using sqlcmd.exe in wix when sql script file has If logic like
---------------------------------
IF NOT EXISTS(select * from syscolumns where Name like 'TransferFlag' and Id = Object_id('hraemployee'))
alter table hraemployee add TransferFlag bit NULL

IF NOT EXISTS(select * from Sys.tables where name='HRMEmployeeTransfer')
begin
CREATE TABLE HRMEmployeeTransfer(
[HRMEmployeeTransferID] [int] IDENTITY(1,1) NOT NULL,
[HRAEmployeeID_Previous] [int] NOT NULL,
[HRAEmployeeID_New] [int] NOT NULL,
[HRAJoiningDate] [datetime] NULL,
[HRATransferedDate] [datetime] NULL,
[From_COMCompanyID] [smallint] NOT NULL,
[To_ComCompanyID] [smallint] NOT NULL,
[From_COMProjectID] [smallint] NOT NULL,
[To_ComProjectID] [smallint] NOT NULL,
[From_HRADepartmentID] [smallint] NOT NULL,
[To_HRADepartmentID] [smallint] NOT NULL,
[FID] [datetime] NULL,
[FIUserID] [smallint] NOT NULL,
[RMD] [datetime] NULL,
[RMUserID] [smallint] NOT NULL,
CONSTRAINT [PK_HRMEmployeeTransfer] PRIMARY KEY CLUSTERED
(
[HRMEmployeeTransferID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
end
--------------------------------------
Error i get is
Msg 2812, Level 16, State 62, Server BUILDEV\BUILD, Line 1
Could not find stored procedure 'I'

Look like sqlcmd is not accepting IF statement of DDL sqlscript.

Pls help

thanks,Rajesh

Anonymous said...

Hi Neil,
The example works fine but the is not removing the file. Please suggest.

Neil Sleightholm said...

Which file do you mean?

Anonymous said...

Thanks for the this. Helped me decipher the QuietExec custom action for IIS install.

Anonymous said...

Hi Neil,

Is there anyway can I delete the Test.sql file right after I run it?

Or how can I include the Test.sql using Binary and reference it to my custom action, so that I don't have to delete Test.sql?

Thanks very much,
-Dao

Neil Sleightholm said...

Dao - I can't think of a simple way of doing this. Really this should be custom action that reads the binary resource.

Anonymous said...

Hi, great post.
Can you add a sample of executing a reverse script for uninstall?
i.e on install create table and on uninstall drop the table.

Neil Sleightholm said...

You are probably better off using the built-in WiX SQL commands for that.

Anonymous said...

Is there a way to capture the output of sqlcmd to a WIX variable/property. Output will be 0 or 1.

Neil Sleightholm said...

I am not aware of any way to do this, you might need to write a custom action to do it.

Anonymous said...

Hi,

How do I hide the SQLCMD window? I dont want it to be seen during installation process.
Thanks!

Neil Sleightholm said...

I didn't think it did display the window as it uses CAQuietExec but looking at newer code the name might have changed. I now use:

Anonymous said...

Hi,

I am trying to put a checkbox in a dialog with background image, but the checkbox is not visible. Its visible only when i will hover over the place, then the checkbox functions well. Can you please help me out.

Thankyou