SMO (SQL Server Management Objects) Errors in Web Deploy UI

In order to enable certain database functionality in the “Refresh” (v1.1) release of the Web Deployment Tool the Web Deploy UI in inetmgr took on a new dependency related to SQL Server Management Objects (“SMO”). In RTW (v1.0) you could do dbFullSql operations in the UI on a remote server when remote managing as long as the remote server had SMO installed, but with Refresh Web Deploy UI, SMO must be installed locally to do dbFullSql operations.

Here’s a summary of where you might see errors due to this new dependency or functionality either for local import/export or remote managing of another server along with a breakdown of the errors:

“ indicates match with above line

Blank Provider Settings for dbFullSql component

ERROR:

You can’t set any Provider Settings for dbFullSql because the options aren’t displayed!

Occurs when:

You are on an RTW (v1) client managing a Refresh (v1.1) server and do not have SMO installed locally (on the client).

Solution:

Install SMO on the client (see end of page for instructions on how to install SMO)

Parameter Entry Kind error when you select the package

ERROR TEXT:

An XML exception occurred.

parameters.xml

The parameter entry kind must be one of the following: (XmlFile, TextFile, TextFilePosition, DeploymentObjectAttribute, DestinationVirtualDirectory, DestinationBinding, ProviderPath).

Requested value 'SqlCommandVariable' was not found.

Occurs when:

You are on an RTW (v1) client or if you have RTW (v1) on the client or server when remote managing.  This error occurs if the package uses new TS Data parameters that are new in Web Deploy Refresh (see this page for more information on TS Data parameters in Refresh: http://blogs.iis.net/krolson/archive/2010/02/02/sqlcmd-scripts-in-web-deploy-msdeploy-v1-1.aspx).

Solution:

Install Web Deploy Refresh (v1.1) on the client (or Server, if this is the RTW box). Web Deploy is available through the Web Platform Installer (http://www.microsoft.com/web/downloads/platform.aspx).

SMO Dependency Error during Export

ERROR TEXT:

The SQL provider cannot run because of a missing dependency. Please make sure that Microsoft SQL Server Management Objects (Version 10) is installed.

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Occurs when:

You add a dbFullSql component while exporting a package when working on a box with Refresh Web Deploy and no SMO installed locally.

Solution:

Install SMO on the client (see end of page for instructions on how to install SMO)

SMO Dependency Error during Import

ERROR TEXT:

Object of type 'package' and path '\\iisdist\release\oob\wpi\v2RTW_TestTools\AppPackages\GalleryServerPro_IIS_2_3_3512.zip' cannot be created.

The Zip package '\\iisdist\release\oob\wpi\v2RTW_TestTools\AppPackages\GalleryServerPro_IIS_2_3_3512.zip' could not be loaded.

The SQL provider cannot run because of a missing dependency. Please make sure that Microsoft SQL Server Management Objects (Version 10) is installed.

Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

Occurs when:

Importing a package with a dbFullSql component when working on a box with Refresh Web Deploy and no SMO installed.

Solution:

Install SMO on the client (see below for instructions on how to install SMO)

Install SMO!

It’s as easy as 1-2-3!

1. Get or start the Web Platform Installer.

You can get the Web Platform Installer here: http://www.microsoft.com/web/downloads/platform.aspx

2. Check the box next to the SMO product

This will be on the Web Platform tab, Database section, with the name: “SQL Server 2008 Management Objects”

3. Click Install