SQLCMD scripts in Web Deploy (MSDeploy) v1.1

Posted: Feb 02, 2010  3 comments  

Average Rating

Tags
:setvar
MSDeploy
SQLCMD
SqlCmdVariable
TS Data
Web Deploy
Web Deployment Tool

Share this Post

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:

clip_image001[17]

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

exportApp

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

clickManageComponents

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

clip_image001

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

clickForProviderSettings

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.

clip_image001[5]

6. Click OK.

closeManageComponents

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

seeScriptHasBeenAdded

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! 

clip_image001[7]

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.

clip_image001[9]

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

clip_image001[11]

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).

clip_image001[13]

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

Comments

The 1.1. release became available yesterday in WebPI. It may not have the same fanfare as usual because it's a "refresh" rather than a standard milestone like RTW was or Beta will be. There will be an announcement for the 1.1 release, but Web Deployment Tool 1.1 is going to act like 1.0, and will work with 1.0, so the existing documentation still applies.  There are a few new items in 1.1, mainly: support for code download, handling of TS data scripts, and the credential store (a blog for this will be published shortly).

Thanks for pointing out that we're still showing 1.0 - we'll work on updating those references.

Feb 03 2010 by krolson

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 (learn.iis.net/.../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: blogs.iis.net/.../msdeploy-custom-provider-to-execute-batch-files.aspx

Jul 16 2010 by krolson

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

GO

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

GO

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?

Sep 29 2012 by Raptor909

Submit a Comment

  • Plain text is accepted.
  • URLs starting with http:// are converted to links.