SqlCodeAnalyzeServerTask

This topic the MSBuild SqlCodeAnalyzeServerTask task that is available with SQL Enlight.

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.

ConnectionString

Required. Connection string to the target context database. If provided, the ServerName, DatabaseName, Username and Password parameters are ignored.

ServerName

Deprecated. Target SQL Server name.

DatabaseName

Deprecated. Target database name. Default is “master”.

Username

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

Password

Deprecated. 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:

Type Description
Server Includes server triggers and all functions,views, procedures and triggers in the server databases.
ServerTrigger Includes server triggers.
View Includes all views in specific database.
Database Includes all functions, views, procedures and triggers in specific database.
DmlTrigger Includes all DML triggers.
DatabaseTrigger Includes all DDL triggers.
UserDefinedFunction Includes all user defined functions.
DatabaseProgrammabilityObject Includes 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 an XML file containing the values for the analysis rules parameters.

Check SchemasAnalysisParameters.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 reportsanalysis1.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]”.

LicenseKey

Required. SQL Enlight for Build Machines license key.

LicenseData

Required. Path to a license activation response XML file.

Verbosity

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

Example

Example MSBuild project SqlCodeAnalyzeServerTask:

Copy
 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)UbitsoftSQL EnlightUbitsoft.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:ProjectsMyProjectBuildReportsAdventureWorks2008R2_Database_AnalysisReport.xml</SqlEnlight_AnalysisReportOutputPath>
16<SqlEnlight_AnalysisRulesParametersFile>C:Program Files (x86)UbitsoftSQL EnlightXmlAnalysisParameters.xml</SqlEnlight_AnalysisRulesParametersFile>
17<SqlEnlight_AnalysisTemplateFile>C:Program Files (x86)UbitsoftSQL EnlightTemplatesAnalysisDefaultAnalysisTemplate.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<SqlEnlight_LicenseKey>ABCD-1234-XYZ-QWER-AAAA</SqlEnlight_Refactorings>
23<SqlEnlight_LicenseData>E:SQL Enlightlicense-activation-response.XML</SqlEnlight_Refactorings>
24</PropertyGroup>
25
26<Target Name="AnalyzeDatabaseTarget">
27
28<Message Text="Starting database analysis..." />
29<Message Text="Running analysis on SQL Server '$(SqlEnlight_AnalysisServerName)' database '$(SqlEnlight_AnalysisDatabaseName)'..." />
30
31<SqlCodeAnalyzeServerTask
32FailOnRuleViolation="false"
33Quiet="true"
34ServerName="$(SqlEnlight_AnalysisServerName)"
35DatabaseName="$(SqlEnlight_AnalysisDatabaseName)"
36ObjectType="$(SqlEnlight_AnalysisObjectType)"
37ObjectName="$(SqlEnlight_AnalysisObjectName)"
38Rules="$(SqlEnlight_AnalysisRules)"
39ReportOutputPath="$(SqlEnlight_AnalysisReportOutputPath)"
40ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
41TemplateFile="$(SqlEnlight_AnalysisTemplateFile)"
42Verbosity="$(SqlEnlight_Verbosity)"
43MaxAge="$(SqlEnlight_MaxAge)"
44LicenseKey="$(SqlEnlight_LicenseKey)"
45LicenseData="$(SqlEnlight_LicenseData)">
46<Output TaskParameter="ViolationsCount" PropertyName="SqlEnlight_AnalysisViolationsCount" />
47<Output TaskParameter="ObjectsCount" PropertyName="SqlEnlight_AnalyzedObjectsCount" />
48</SqlCodeAnalyzeServerTask>
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 analysis!" />
54
55</Target>
56
57
58
59</Project>

See Also