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
GO
create database $(databaseName)
GO
use $(databaseName)
GO
create table $(tableName)(name varchar(50))
GO
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')
GO

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:

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