Database versioning tools such as SQL Source Control and SQL Change Automation help development teams author their SQL scripts. These include programmable objects (stored procedures, functions, triggers etc) and migration scripts (creating and modifying table structure and data). The Code Analysis command line allows organization to apply a set of standards to these SQL scripts as part of a continuous integration process. However, it is often preferable to identify these issues before they disrupt the development team by causing the build to break.

Although SQL Prompt helps a lot by identifying issues "as you type" in your IDE, another approach is to configure a pre-commit hook. This is a process that executes as part of a VCS commit operation, and can be used to abort a commit if criteria are not met, such as code analysis rules failing.

To configure a pre-commit hook in Git, create a text file called pre-commit and place it in in your .git/hooks/ folder.

#!/bin/sh
#
echo "Running code analysis in pre commit hook"
"C:\SCG4\SqlCodeGuard.Cmd.exe"  /source:MyDatabaseFiles /config:MyRules.casettings /outfile:CodeAnalysisIssues.html /log:CodeAnalysisLog.log
x=$?

if [ $x == 1 ]
then	
	echo "Opening CodeAnalysisIssues.html in your browser to review issues"
	start CodeAnalysisIssues.html
fi
echo exit code = $x
exit $x

In this example, the code analysis command line (SQLCodeGuard.exe) is referencing a code analysis rules configuration file (.casettings). This is an xml file where the desired rules are listed with user-configurable severities (ignore, warning and error). This file can be exported from SQL Prompt (Manage Code Analysis Rules menuitem) and should be saved as part of your project. This means that it can be not only referenced by each developer's pre-commit hook, but also as the input configuration for your CI check.

By default SQL Prompt sets all rules in the .casettings file to the "Warning" severity. For the Git hook to fail you will need to configure the desired rules to use the "Error" severity.

Once the Git hook file is in place, any attempt to commit will apply the chosen rules to your folder of database scripts, and will output a report (CodeAnalysisIssues.html in the example above).

Known issues

• You can configure rules with "Ignore" and "Warning" severities in SQL Prompt, but not "Error". This needs to be done by manually editing the xml file.
• The code analysis command line outputs a log file each time it is run, which will need to be cleaned up manually for now (eg, place logic into the Git hook script to remove the files). To mitigate this use /log:<logfilename.log> and gitignore this file.
• Detected code analysis issues aren't displayed in the console output, so the report (html or xml) needs to be consulted.
• If you have more than one folder with .sql scripts, you will need to invoke the sqlcodeguard.exe command twice, as /source can only take one value.