Click or drag to resize
MSBuild

This topic describes the MSBuild tasks available with SQL Enlight.

SqlCodeAnalyzeFilesTask

The task runs static analysis over specified script files and creates a report.

Parameters

Name

Description

Quiet Suppresses all except error output messages.
InputPath Required. Input script files location.

Multiple files can be provided by separating each file with semicolon. The '-' prefix can be used to exclude specific files.

Example:

"c:\my files\*.sql" or "c:\my files\file1.sql;c:\my files\file2.sql;"

Rules List of analysis rules or analysis groups which to be applied. The rules can be separated with ',',';' or '|'. Example: sa0001,sa0002,ex0018,Performance,Design
ParametersFile Path to a XML file containing the values for the analysis rules parameters. Check the Xml\AnalysisParameters.xml for example and the Schemas\AnalysisParameters.xsd for the parameters file XML schema.
ContextFile Path to an analysis context file which to be used for analyzing the target script files. Analysis context file for specific database can be created using the command line tool with the analysiscontext command.

ServerName

Optional. Context SQL Server name.

DatabaseName

Optional. Context database name.

Username

Optional. Username for accessing the context database. If omitted integrated security will be used.

Password

Optional. User password for accessing the context database. If omitted integrated security will be used.

TemplateFile

Optional parameter for setting external analysis template. The external template will be used as a source for the analysis rules instead of the default template.

See Analysis Template Import/Export topic for information how to save existing template in a file.

ReportOutputPath Output location of the analysis report. Example: "c:\my reports\analysis1.xml"
ReportStylesheetPath XSLT document that to be used to transform the report XML. Example: "C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt"
FailOnRuleViolation If TRUE, the task will fail if a single rule violation is found, otherwise the build script can decide whether to fail the build or not. Default value is FALSE.
ViolationsCount Output parameter returning the total number of rule violations. The number includes all rule violations not considering their type.
ObjectsCount Output parameter returning the total number analyzed objects.
IncludeSubfolders Include files in sub folders.

MaxAge

Optional. MAXimum file or database object AGE - exclude objects older than given date or time interval. When date is provided, can be any valid culture specific date time string(e.g. "2/16/2015 12:15:12 PM"). If age is provided in time interval, the valid format is "d | [d.]hh:mm[:ss]".

Verbosity

Output verbosity level. Supported values: quiet, normal and detail.

SettingsFile

A settings file which to override the current user's settings XML file.

Can be used to override some of the options which are not available as parameters for the command line tool, MSBuild and NAnt tasks, but are configurable using SQL Enlight user interface.

See Settings XML File for more details.

Example

Example MSBuild project SqlCodeAnalyzeFilesTask

 1<?xml version="1.0" encoding="utf-8"?>
 2<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 3
 4<UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeFilesTask"
 5AssemblyFile="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
 6
 7<PropertyGroup>
 8<SqlEnlight_AnalysisInputPath>E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql</SqlEnlight_AnalysisInputPath>
 9<SqlEnlight_AnalysisServerName>.</SqlEnlight_AnalysisServerName>
10<SqlEnlight_AnalysisDatabaseName>AdventureWorks2008R2</SqlEnlight_AnalysisDatabaseName>
11<SqlEnlight_AnalysisUsername></SqlEnlight_AnalysisUsername>
12<SqlEnlight_AnalysisPassword></SqlEnlight_AnalysisPassword>
13<SqlEnlight_AnalysisRules>ex0018,Design,Performance</SqlEnlight_AnalysisRules>
14<SqlEnlight_AnalysisReportOutputPath>E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Script_AnalysisReport.html</SqlEnlight_AnalysisReportOutputPath>
15<SqlEnlight_ReportStylesheetPath>C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt</SqlEnlight_ReportStylesheetPath>
16<SqlEnlight_AnalysisRulesParametersFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml</SqlEnlight_AnalysisRulesParametersFile>
17<SqlEnlight_AnalysisContextFile>E:\Projects\MyProject\Build\Context\AdventureWorks2008R2.xml</SqlEnlight_AnalysisContextFile>
18<SqlEnlight_AnalysisTemplateFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml</SqlEnlight_AnalysisTemplateFile>
19<SqlEnlight_AnalyzedObjectsCount>0</SqlEnlight_AnalyzedObjectsCount>
20<SqlEnlight_AnalysisViolationsCount>0</SqlEnlight_AnalysisViolationsCount>
21<SqlEnlight_MaxAge>5</SqlEnlight_MaxAge>
22<SqlEnlight_Verbosity>detail</SqlEnlight_Verbosity>
23</PropertyGroup>
24
25
26<Target Name="AnalyzeSqlScriptsTarget">
27
28<Message Text="Starting SQL script analysis..." />
29<Message Text="Running analysis on SQL script '$(SqlEnlight_AnalysisInputPath)'..." />
30
31<SqlCodeAnalyzeFilesTask
32FailOnRuleViolation="false"
33Quiet="true"
34Rules="$(SqlEnlight_AnalysisRules)"
35InputPath="$(SqlEnlight_AnalysisInputPath)"
36ReportOutputPath="$(SqlEnlight_AnalysisReportOutputPath)"
37ReportStylesheetPath="$(SqlEnlight_ReportStylesheetPath)"
38ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
39ContextFile="$(SqlEnlight_AnalysisContextFile)"
40ServerName="$(SqlEnlight_AnalysisServerName)"
41DatabaseName="$(SqlEnlight_AnalysisDatabaseName)"
42TemplateFile="$(SqlEnlight_AnalysisTemplateFile)"
43Verbosity="$(SqlEnlight_Verbosity)"
44MaxAge="$(SqlEnlight_MaxAge)">
45<Output TaskParameter="ViolationsCount" PropertyName="SqlEnlight_AnalysisViolationsCount" />
46<Output TaskParameter="ObjectsCount" PropertyName="SqlEnlight_AnalyzedObjectsCount" />
47
48</SqlCodeAnalyzeFilesTask>
49
50<Error Text="SQL Enlight analysis reported $(SqlEnlight_AnalysisViolationsCount) rule violations, see $(SqlEnlight_AnalysisReportOutputPath) for details."
51Condition="$(SqlEnlight_AnalysisViolationsCount) > 0" />
52
53<Message Text="Completed database script files analysis!" />
54</Target>
55
56</Project>
SqlCodeAnalyzeServerTask

The task runs static analysis over specified SQL Server objects and creates a report.

Parameters

Name

Description

Quiet

Suppresses all except error output messages.

ServerName

Required. Target SQL Server name.

DatabaseName

Target database name. Default is "master".

Username

Username for accessing the database. If omitted integrated security will be used.

Password

User password for accessing the database. If omitted integrated security will be used.

ObjectType

Required. Type of the objects that will be analyzed.

The allowed object types are:

TypeDescription
ServerIncludes server triggers and all functions,views, procedures and triggers in the server databases.
ServerTriggerIncludes server triggers.
ViewIncludes all views in specific database.
DatabaseIncludes all functions, views, procedures and triggers in specific database.
DmlTriggerIncludes all DML triggers.
DatabaseTriggerIncludes all DDL triggers.
UserDefinedFunctionIncludes all user defined functions.
DatabaseProgrammabilityObjectIncludes functions , database triggers and stored procedures.
StoredProcedure Includes all stored procedures.

ObjectName

This parameter specifies the name of the target objects and supports one of three options:

  • When the value of the parameter is set to '*' or not provided, all objects of the type specified by ObjectType will be matched.

  • For match exact object, the single-part delimited name for server triggers or two-part delimited name for database functions, views and stored procedures: [server_trigger_name] or [schema_name].[object_name]

  • When the value is enclosed in forward slashes, it is threated as regular expression literal and is matched against the fully qualified object name.

Examples:

For analyzing a exact database object: [dbo].[mysp_MyStoredProcedure]

For analyzing all objects of a particular schema: /\[schema_name\]\.\[.*\]/

TemplateFile

Optional parameter for setting external analysis template. The external template will be used as a source for the analysis rules instead of the default template.

See Analysis Template Import/Export topic for information how to save existing template in a file.

Rules

List of analysis rules or analysis group names separated with ',',';' or '|'.

Example: rule1,rule2,rule3,Performance,rule12.

ParametersFile

Path to a XML file containing the values for the analysis rules parameters.

Check Schemas\AnalysisParameters.xsd for the parameters file XML schema.

ContextFile

Path to an analysis context file which to be used for analyzing the tagret script files.

Analysis context file for specific database can be created using the command line tool with the analysiscontext command.

ReportOutputPath

Output location of the analysis XML report.

Example: "c:\my reports\analysis1.xml"

FailOnRuleViolation If TRUE, the task will fail if a single rule violation is found, otherwise the build script can decide whether to fail the build or not. Default value is FALSE.
ViolationsCount Output parameter returning the total number of rule violations. The number includes all rule violations not considering their type.
ObjectsCount Output parameter returning the total number analyzed objects.

MaxAge

Optional. MAXimum file or database object AGE - exclude objects older than given date or time interval. When date is provided, can be any valid culture specific date time string(e.g. "2/16/2015 12:15:12 PM"). If age is provided in time interval, the valid format is "d | [d.]hh:mm[:ss]".

Verbosity

Output verbosity level. Supported values: quiet, normal and detail.

Example

Example MSBuild project SqlCodeAnalyzeServerTask:

 1<?xml version="1.0" encoding="utf-8"?>
 2<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 3
 4<UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeServerTask"
 5AssemblyFile="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
 6
 7<PropertyGroup>
 8<SqlEnlight_AnalysisServerName>.</SqlEnlight_AnalysisServerName>
 9<SqlEnlight_AnalysisDatabaseName>AdventureWorks2008R2</SqlEnlight_AnalysisDatabaseName>
10<SqlEnlight_AnalysisUsername></SqlEnlight_AnalysisUsername>
11<SqlEnlight_AnalysisPassword></SqlEnlight_AnalysisPassword>
12<SqlEnlight_AnalysisObjectType>database</SqlEnlight_AnalysisObjectType>
13<SqlEnlight_AnalysisObjectName>AdventureWorks2008R2</SqlEnlight_AnalysisObjectName>
14<SqlEnlight_AnalysisRules>ex0018,Design,Performance</SqlEnlight_AnalysisRules>
15<SqlEnlight_AnalysisReportOutputPath>E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Database_AnalysisReport.xml</SqlEnlight_AnalysisReportOutputPath>
16<SqlEnlight_AnalysisRulesParametersFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml</SqlEnlight_AnalysisRulesParametersFile>
17<SqlEnlight_AnalysisTemplateFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml</SqlEnlight_AnalysisTemplateFile>
18<SqlEnlight_AnalyzedObjectsCount>0</SqlEnlight_AnalyzedObjectsCount>
19<SqlEnlight_AnalysisViolationsCount>0</SqlEnlight_AnalysisViolationsCount>
20<SqlEnlight_MaxAge>2015-05-31 11:30:00</SqlEnlight_MaxAge>
21<SqlEnlight_Verbosity>detail</SqlEnlight_Verbosity>
22</PropertyGroup>
23
24<Target Name="AnalyzeDatabaseTarget">
25
26<Message Text="Starting database analysis..." />
27<Message Text="Running analysis on SQL Server '$(SqlEnlight_AnalysisServerName)' database '$(SqlEnlight_AnalysisDatabaseName)'..." />
28
29<SqlCodeAnalyzeServerTask
30FailOnRuleViolation="false"
31Quiet="true"
32ServerName="$(SqlEnlight_AnalysisServerName)"
33DatabaseName="$(SqlEnlight_AnalysisDatabaseName)"
34ObjectType="$(SqlEnlight_AnalysisObjectType)"
35ObjectName="$(SqlEnlight_AnalysisObjectName)"
36Rules="$(SqlEnlight_AnalysisRules)"
37ReportOutputPath="$(SqlEnlight_AnalysisReportOutputPath)"
38ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
39TemplateFile="$(SqlEnlight_AnalysisTemplateFile)"
40Verbosity="$(SqlEnlight_Verbosity)"
41MaxAge="$(SqlEnlight_MaxAge)">
42<Output TaskParameter="ViolationsCount" PropertyName="SqlEnlight_AnalysisViolationsCount" />
43<Output TaskParameter="ObjectsCount" PropertyName="SqlEnlight_AnalyzedObjectsCount" />
44</SqlCodeAnalyzeServerTask>
45
46<Error Text="SQL Enlight analysis reported $(SqlEnlight_AnalysisViolationsCount) rule violations, see $(SqlEnlight_AnalysisReportOutputPath) for details."
47Condition="$(SqlEnlight_AnalysisViolationsCount) > 0" />
48
49<Message Text="Completed database analysis!" />
50
51</Target>
52
53
54
55</Project>
See Also