SQLCMD scripts in Web Deploy (MSDeploy) v1.1

New in the v1.1 release of the Web Deployment Tool (MSDeploy) is the ability to deploy .sql scripts which use SQLCMDs such as “:setvar” (a.k.a. TS Data scripts).  Additionally, when using such scripts during the import or export of a package in the UI, :setvar variables will be automatically parameterized.

Suppose you have a simple script which uses SQLCMD, with contents like the following:

:setvar databaseName TestDatabase
:setvar tableName    TestTable
create database $(databaseName)
use $(databaseName)
create table $(tableName)(name varchar(50))
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')
Insert into $(tableName) values('first')

If you try to use this script with the dbFullSql provider to create a database in RTW Web Deploy, you’ll see errors due to the :setvar SQL CMD:


Refresh Web Deploy (v1.1) will handle these scripts, and will also automatically parameterize your SQL CMD variables if you use the Web Deploy Inetmgr UI.  Here’s an example:

1. Open Inetmgr and select an application to export click the “Export Application…” task in the Actions pane


2. In the resulting Export Application wizard, click “Manage Components…”


3. Add a “dbFullSql” provider using the location of your SQLCMD script as the path


4. Click the Provider Settings column and use the “…” button to open the Provider Settings dialog


5. Set the “Transacted” setting to “false”. (Note: the import of SQLCMD scripts will fail if you do not set transacted=false AND have CREATE DATABASE or CREATE FULLTEXT CATALOG statements in the script.   The export will still work if you do not set the transacted setting, however it is not possible to set this transacted setting during import in the UI, so it is highly recommended that you do this step during export.) Click Close.


6. Click OK.


7. Now you can see the script added to the package under Deploy SQL Database. Click Next.


8. On this page you can see 3 parameters related to your script: “Parameter 2” is the SqlConnectionString for the script. There are also automatically generated “SqlCmdVariable” parameters – one for each :setVar variable specified in the script! 


9. You can also see that these are using a new Parameter Entry Type – select one of the SqlCmdVariable parameter’s Parameter Entries and click Edit… to see how this is used.  This parameter entry says it applies to a variable called “databaseName” within the script at D:\simplescript.sql.  Click OK or Cancel to close the edit dialog and then click Next.


10. Specify where to save your package and click Next. That’s it!


Now, if you want to import a package with a TS Data script, such as the one just created, you’ll find that the SqlCmdVariable parameters allow you to change the script :setvar variables (such as replacing the default databaseName variable “TestDatabase” with “MyCoolApp_db”).  They also have a nice description to let you know which variable it refers to in the script. The default value will be what is written in the script for that variable (i.e. the script default).


Note on other SQLCMD directives

Web Deploy v1.1 will be able to deploy scripts with SQL CMDs, but with the exception of :setVar, many of the commands will be either be considered “no-ops” (“no operation” – they will be effectively ignored) or will result in an exception. The following table shows whether use of a given SQLCMD will result in a no-op or failure by noting what Web Deploy does when it encounters the directive:

SQLCMD Web Deploy action
:RESET none
:ED none
:!! throw exception
:QUIT throw exception
:EXIT throw exception
:r throw exception
:ServerList none
:Setvar supported, can be parameterized
:List none
:Error none
:Out none
:Perftrace none
:Connect throw exception
:On Error none
:Help throw exception
:Xml none
:Listvar none

You can find more information on these SQLCMDs and what they do here: http://msdn.microsoft.com/en-us/library/ms162773.aspx


  • You can certainly use Web Deploy on IIS6/Server 2003 and if you are asking about deploying scripts to SQL server - yes, this works, too. This page (http://learn.iis.net/page.aspx/346/web-deploy/) has walkthroughs for Web Deploy including one on syncing an IIS 6 website to IIS 6 as well as one for syncing IIS 6 to IIS 7.

    I do not believe there is any open source code available for the tool itself, but there are examples of code for writing additional custom providers, if there is some functionality you would like that is not already in the product. For example, see this page: http://blogs.iis.net/kateroh/archive/2009/06/19/msdeploy-custom-provider-to-execute-batch-files.aspx

  • I've followed this example in an attempt to deploy a database with a parameterized SQL file and I get the following errors:

    [9/29/2012 6:34:15 PM] Parameter entry 'Parameter 1/1' is applicable to 'dbFullSql/c:\inetpub\wwwroot\auditOL\UpdateClientData.sql' because of its scope.
    [9/29/2012 6:34:15 PM] Adding child sqlScript (MSDeploy.dbFullSql/dbFullSql[@path='c:\inetpub\wwwroot\auditOL\UpdateClientData.sql']/sqlScript).
    [9/29/2012 6:34:15 PM] Getting stream data for 'sqlScript' ('MSDeploy.dbFullSql/dbFullSql[@path='c:\inetpub\wwwroot\auditOL\UpdateClientData.sql']/sqlScript').
    [9/29/2012 6:34:15 PM] Parameter entry 'SQLCmdVariable 1/1' could not be applied anywhere.
    [9/29/2012 6:34:15 PM] Parameter entry 'SQLCmdVariable 2/1' could not be applied anywhere.
    [9/29/2012 6:34:15 PM] Parameter entry 'SQLCmdVariable 3/1' could not be applied anywhere.
    [9/29/2012 6:34:15 PM] The dependency check 'DependencyCheckInUse' found no issues.
    [9/29/2012 6:34:15 PM] The synchronization completed in 1 pass(es).

    Here's my script:

    :setvar clientID CLIENT
    :setvar auditDB AUDIT
    :setvar caseDB CASE

    USE YCN_DB_Client

    UPDATE ClientMaster SET [clientCode] = $(clientID), dbServerConnectionString = $(auditDB), dbServerCaseConnectionString = $(caseDB) WHERE [clientCode] = 'paws'

    When I do the deploy I can see the script displayed in the window, etc. The databases are successfully created and I can run the application as long as I manually set the 3 variables after the install finishes. Anybody have an idea what I did wrong?

Comments have been disabled for this content.