We have had a lot of requests from users asking us to make T-SQL Static Code Analysis available at build time and from the command line. The latest Visual Studio Team System 2008 Database Edition Power Tool release, adds this ability to the product through a new MSBuild task. In this blog post I will go through the step that you need to follow to enable and use T-SQL Static Code Analysis from the command line.
Step 0: Install the Power Tools
You first need to install the latest Power Tools for Visual Studio Team System 2008 Database Edition.
Step 1: Create a new database project or open an existing one
We need a database project, either use an existing one, or simple create a test project, from example using the pubs database. That is what I will use for this example.
Step 2: Unload the project
We will need to add a entry to the project file to import the MSBuild .targets file for Power Tools. In order to do this you have to unload the project so that you can edit project file using the XML editor. Right click on the project node inside the Solution Explorer and choose “Unload Project”.
Step 3: Import the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file
The next step is to modify the project file to add an import statement to include the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file which contains the MSBuild task for T-SQL Static Code Analysis. Right click on the unloaded project in Solution Explorer and choose “Edit <project name.dbproj>”.
This will open the .dbproj file inside the Visual Studio XML editor. Now we need to add an import statement to load the .target file. If you search for “<Import” inside the project file you will find the regular import which loads the normal MSBuild tasks for the database project like the SqlBuildTask and the SqlDeployTask.
We need to add a reference to the Power Tools targets file.
Step 4: Reload the project
After you made the project change, including the new targets file, it is time to reload the project. Right click on the unloaded project node inside Solution Explorer and choose “Reload Project”. This will reload the project.
NOTE: Make sure you have no errors when loading the project, because this will be an indication that your edits were not correct.
Step 5: Start a “Visual Studio 2008 Command Prompt”
The next step is to run T-SQL Static Code Analysis from the command line, in order to do so we need a command prompt that has the PATH set correctly so we can call MSBuild.exe. Visual Studio creates a shortcut in the menu structure that will launch a command prompt with the correct settings.
When you started the command prompt navigate to the directory where you project file is located that you changed in the previous step.
Step 6: Run T-SQL Static Code Analysis from the command-line
From the command prompt run: msbuild pubs.dbproj /t:SqlAnalysis
NOTE: The results file is created in the output directory, which by default is the SQL directory
Step 7: Look at the results
When executing the project using the SqlAnalysis target, an XML file is created that contains the results, named StaticCodeAnalysis.Results.xml. This file contains the information that is normally displayed in the Visual Studio “Error List”.
When you open the file in VS it will look like this:
Step 8: Clean up the results
If you want to clean up the resulting file produced by the static code analysis run, simply run:
msbuild pubs.dbproj /t:ScaClean
This will remove the results file.
Following the steps outlined before enable you to run T-SQL Static Code Analysis from the command line, you can use the same step to integrate this in to your Team Build environment, but you have to make sure that you install the Power Tools on the build server before you can use this.
“DataDude” Group Engineering Manager