Microsoft Visual Studio Team System 2008 Database Edition

Power Tools

February 2008 Release

This release updates the Visual Studio 2005 version of thepower tools to work with Visual Studio Team System 2008. This release also provides several new features, which are detailed later in this document. These new and updated features might be incorporated into a future release of Database Edition.

To use these tools, you must have the following prerequisites:

Visual Studio Team System 2008 Database Edition or Team Suite must be installed.
Visual Basic or C# must be installed.
MSXML must be installed. (It is installed by default with Visual Studio.)
Microsoft SQL Server 2005 Express, Developer, Standard, or Enterprise Edition must be installed and running. For more information, see the Microsoft Web site (
Windows Vista, Windows XP with Service Pack 2 (SP2), Windows Server 2003 with Service Pack 1 (SP1), or Microsoft Windows 2000 with Service Pack 4 (SP4) must be installed.

1.New Features in this Release

1.1.Improvements in Database Unit Testing

In this release, two new test conditions can help verify the results of your database unit tests.

1.1.1.ChecksumCondition

Description: By using this test condition, you can verify that the DataSet returned when you run a unit test is the expected DataSet. The test condition stores the checksum of the DataSet at design-time and compares that checksum to the checksum of the DataSet that is returned when you run the test.

Usage: Open the Unit Test Designer.In the Test Conditions list, click ChecksumTestCondition. To configure the test condition, open the Properties window, and click the ellipsis in the Configuration property. A dialog box appears in which you can specify additional information required for the test condition.

1.1.2.ExpectedSchemaTestCondition

Description: By using this test condition, you can verify that the schema of the DataSet returned when you run a unit test matches an expected DataSet schema. The test condition compares the column names and data types. You could use this test condition to verify that the “shape” of a result set matches the expected result.

Usage: Open the Unit Test Designer.In the Test Conditions list, click ExpectedSchemaTestCondition. To configure the test condition, open the Properties window, and click the ellipsis in the Configuration property. A dialog box appears in which you can specify additional information required for the test condition.

1.2.Improvements for Data Generator

Description: You can use theData Generation Wizard to create a data generation plan that copies data from one database and inserts it into another database. This data generation plan is useful if you want to use live data for most of your testing needs but make some minor changes to ensure privacy.

Usage: Open the database project to which you want to add the data generation plan. In Solution Explorer, expand the database project, and click the Data Generation Plans folder. On the Project menu, click Add New Item. In the Add New Item dialog box, click Data Generation Wizard, and follow the prompts.

1.3.Improvements in MSBuild Tasks

1.3.1.SqlAnalysis Task

Description: You can use this task to perform static code analysis. Results of the analysis are saved to StaticCodeAnalysis.Results.xml. By default, this file is created under the $(OutDir) folder, but you can specify the path and filename as shown below. The task uses the settings in the project file to determine which rules are enabled and the severity of those rules. The task also supports the settings that suppress warning messages for the project. Before you can perform static code analysis on a database project, you must add the following Import element to your database project (.dbproj) file:

ImportProject="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v8.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.SqlCodeAnalysis.targets" />

Usage: To performstatic code analysis on the objects in your database project, type the following command:

MSBuild /t:SqlAnalysis /p:ResultFile=”FullPathForStaticCodeAnalysisResults”

1.3.1.ScaClean Task

Description: You can use this task to delete the file that contains the results of the static code analysis that you performed on your database.

Usage: To delete this file, type the following command:

MSBuild /t:ScaClean /p:ResultFile=”FullPathForStaticCodeAnalysisResults”

2.Features Included in the Previous Release

2.1.Improvements to Database Refactoring

2.1.1.Move Schema

Description: You can use this tool to move a database object to a different schema. When you use this tool, a dialog box appears, prompting you to specify the schema to which you want to move the object. The object is moved, and the operation is saved to the refactoring log. This tool will not rename the file that contains the object’s definition.

Usage: To use this tool, open Schema View, and click the object that you want to move. On the Data menu, point to Refactoring, and click Move Schema.

2.1.2.Fullyqualify Name

Description: You can use this tool to disambiguate references, including those that rely on aliases.

Usage: To use this tool, open the Transact-SQL editor, and highlight the name that you want to qualify. On the Data menu, point to Refactoring, and click Fully Qualify Name.

2.1.3.Wildcard Expansion

Description: You can use this tool to expand wildcard references, including those that rely on aliases.

Usage: To use this tool, open the Transact-SQL editor, and highlight the wildcard that you want to expand. On the Data menu, point to Refactoring, and click Wildcard Expansion.

2.2.Deployment Script Generation

Description: You can use this tool to generate scripts from the refactoring log and then add those scripts to your deployment scripts to preserve the intent of refactoring operations. For example, when you use refactoring to rename a database object or move that object to a different schema, the refactoring log captures the fact that you renamed the object. Without this tool, when you rename a table column and then deploy the table, the old table is dropped and re-created because the intent of the operation is unclear. You can manually run the generated script to preserve the intent of the change.

Usage: On the Data menu, point to Refactoring, and click Refactor Command Generator. When the dialog box appears, you must specify the refactoring log file that you want to use to generate the update script. You can also specify the name of the script file that is generated when you click OK.

Command-line Usage: At the command prompt, you can type the following command to generate the update log:

Refactorcmdgen.exe RefactoringLogFilePathAndName OutputScriptName.sql

2.3.Improvements in MSBuild tasks

2.3.1.SqlSchemaCompareTask

Description: You can use this tool to compare two schemas as part of a build process.

Usage: To quickly compare two databases at the command prompt, type the following:

MSBuild /t:SqlSchemaCompareTask /p:SourceConnectionString=”ValidConnectionString1” /p:SourceDatabaseName=”DatabaseName1” /P:TargetConnectionString=”ValidConnectionString2” /p:TargetDatabaseName=”DatabaseName2” /p:OutputPath=”FullPathForSchemaCompareResults” /p:OutputFilename=”FilenameForSchemaCompareResults”

For a complete list of properties that you can set to control the comparison, refer to Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.xsd. You can find that file in [Program Files]\Microsoft Visual Studio 8\Xml\Schemas\LCID\MSBuild, where LCID is your language ID (1033 for English(United States)).

2.3.2.Data Compare Build Task

Description: You can use this tool to compare the data of two databases as part of a build process.

Usage: To quickly compare the data of two databases at the command prompt, type the following:

MSBuild /t:SqlDataCompareTask /p:SourceConnectionString=”ValidConnectionString1” /p:SourceDatabaseName=”DatabaseName1” /P:TargetConnectionString=”ValidConnectionString2” /p:TargetDatabaseName=”DatabaseName2” /p:OutputPath=”FullPathForDataCompareReults”

/p:OutputFilename=”FilenameForDataCompareResults”

2.4.Improvements to Data Generator

2.4.1.Editor for Regular Expression Strings

Description: You can use this tool to more easily specify regular expressions when you edit your data generation plans. When you use the tool to specify a regular expression, you can select from a list of pre-defined expressions, define and save your own expressions, and preview the results of your expressions.

If you are using Windows Vista, the list of pre-defined expressions is stored in the your application data directory: C:\Users\UserName\AppData\Roaming\RegexHelperConfig.xml

Usage:

To use the new editor forregular expressions, you must perform the following steps:

Open a data generation plan.
Click the table for which you want to generate data.
Open the Column Details window.
In the Generator column, for the database column that you want to fill with data, click Regular Expression.
In the column to the right of the Generator Output column, click the browse (…) button.
The Regular Expression Builder for Data Generation dialog box appears.
In Regular expression name, click the name of a predefined regular expression, or click <Add new expression> to define your own expression. After you define the expression, you can click Save to save it for later use.
After you define a regular expression that you want to use, click OK to assign it to the column from which you opened the dialog box.

2.4.2.Editor for Data Bound Columns

Description: You can use this tool to more easily specify a connection and build a SELECT statement when you edit your data generation plans.

Usage:

To use the new Databound Generator editor, you must:

Open a data generation plan.
Click the table for which you want to generate data.
Open the Column Details window.
In the Generator column, for the database column that you want to fill with data, click Data Bound Generator.
In the column to the right of the Generator Output column, click the browse (…) button.
The Data Generator Wizard – Databound Generator dialog box appears.
In Data SourceConnection, click the connection through which you want to select data. If a connection does not already exist, click <Add new connection>.
In Define the Select Statement, type the SELECT statement that you want to use to retrieve the data.Include the ORDER BY clause if you want to insert the data in a specific order.
After you define a connection and a SELECT statement that you want to use, click OK to assign those values to the column from which you opened the dialog box.

Sequential Data Bound Generator

Description: You can use this tool to fill a column with all values that you specify with a query, in the order that you specify. This tool differs from the regular data bound generator, which selects random rows from the result set that you specify with your query.

Usage: To use this tool, you must perform the following steps:

Open a data generation plan.
Click the table for which you want to generate data.
Open the Column Details window.
In the Generator column, for the database column that you want to fill with data, click Sequential Data Bound Generator.
Click the browse (…) button in the column to the right of the Generator Output column.
The Data Generator Wizard – Databound Generator dialog box appears.
In Data SourceConnection, click the connection through which you want to select data. If a connection does not already exist, click <Add new connection>.
In Define the Select Statement, type the SELECT statement that you want to use to retrieve the data.Include the ORDER BY clause if you want the data to be inserted in a specific order.

2.5.Other New Features

2.5.1.Dependency Viewer

Description: You can use this tool to view the dependencies for one or more database objects. You can add one or more database objects to the Dependency Viewer and then browse the dependency tree.

Usage: To use the Dependency Viewer, you must perform the following steps:

In Schema View, right-click the object for which you want to view dependencies, and then click View Dependencies.

The Schema Dependency Viewer window appears.

Expand the node for the object that you added to the Schema Dependency Viewer to examine the objects that it references or that reference it.
Add other objects to the Schema Dependency Viewer by repeating step 1.

In addition:

You can remove an object from the Schema Dependency Viewer by right-clicking the object and then clicking Remove.
You can view the file that contains any object that you add to the Schema Dependency Viewer by right-clicking that object and then clicking View File in Solution Explorer.
You can view the schema object in Schema View by right-clicking the object in the Schema Dependency Viewer and then clicking View Object in Schema View.

2.5.2.Transact-SQL (T-SQL) Static Code Analysis

Description: You can run this tool on your database objects as part of your build process or on-demand to identify common problems in T-SQL coding. You can configure which rules are applied, which are treated as warnings, and which are treated as errors. You can also suppress warnings for a database object.

Usage:

To configure T-SQL Static Code Analysis:

On the Data menu, point to Static Code Analysis, and click Configure.

The Static Analysis tab of the properties for the database project appears.

Select the check boxes for the rules that you want to apply when you run T-SQL Static Code Analysis.
For each rule, you can specify whether the rule is treated as a warning (default) or as an error by double-clicking in the Status column for that rule.
On the File menu, click Save All to save your changes to the rules.

To run Transact-SQL Static Code Analysis:

On the Data menu, point to Static Code Analysis, and click Run.

T-SQL Static Code Analysis runs on your database project. Warnings and errors appear in the Error List window.

To suppress a warning for a database object:

In the Error List window, right-click the warning that you want to suppress, and click Suppress Static Code Analysis Message(s).

An entry is added to the StaticCodeAnalysis.SuppressMessages.xml file, which is in your database project.

Note: To stop suppressing a warning for a database object, you must edit the StaticCodeAnalysis.SuppressMessages.xml file to remove the entry for that object and rule number.

2.5.3.Schema View extensibility

Description: You can develop Visual Studio add-ins that use APIs that one of the assemblies for these tools exposes.

Usage: For more information, see the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.chm reference document that is installed in the DBPro\PowerTools folder in your installation directory for Visual Studio. You can find a sample add-in onCodeplex (