SqlCodeAnalyzeFilesTask

Introduction

This topic the MSBuild SqlCodeAnalyzeFilesTask task that is 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.
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. (See allowed object types below).
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 treated as regular expression literal and is matched against the fully qualified object name. Examples: For analyzing an 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 ’
ParametersFile	Path to an 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 target 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” or “[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.

Allowed Object Types

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.

                                                                                                                                                                                                                                            |

Example

Example MSBuild project SqlCodeAnalyzeFilesTask

              <?xml version="1.0" encoding="utf-8"?>
              <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

              <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeFilesTask"
              AssemblyFile="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />

              <PropertyGroup>
              <SqlEnlight_AnalysisInputPath>E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql</SqlEnlight_AnalysisInputPath>
              <SqlEnlight_AnalysisServerName>.</SqlEnlight_AnalysisServerName>
              <SqlEnlight_AnalysisDatabaseName>AdventureWorks2008R2</SqlEnlight_AnalysisDatabaseName>
              <SqlEnlight_AnalysisUsername></SqlEnlight_AnalysisUsername>
              <SqlEnlight_AnalysisPassword></SqlEnlight_AnalysisPassword>
              <SqlEnlight_AnalysisRules>ex0018,Design,Performance</SqlEnlight_AnalysisRules>
              <SqlEnlight_AnalysisReportOutputPath>E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Script_AnalysisReport.html</SqlEnlight_AnalysisReportOutputPath>
              <SqlEnlight_ReportStylesheetPath>C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt</SqlEnlight_ReportStylesheetPath>
              <SqlEnlight_AnalysisRulesParametersFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml</SqlEnlight_AnalysisRulesParametersFile>
              <SqlEnlight_AnalysisContextFile>E:\Projects\MyProject\Build\Context\AdventureWorks2008R2.xml</SqlEnlight_AnalysisContextFile>
              <SqlEnlight_AnalysisTemplateFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml</SqlEnlight_AnalysisTemplateFile>
              <SqlEnlight_AnalyzedObjectsCount>0</SqlEnlight_AnalyzedObjectsCount>
              <SqlEnlight_AnalysisViolationsCount>0</SqlEnlight_AnalysisViolationsCount>
              <SqlEnlight_MaxAge>5</SqlEnlight_MaxAge>
              <SqlEnlight_Verbosity>detail</SqlEnlight_Verbosity>
              <SqlEnlight_LicenseKey>ABCD-1234-XYZ-QWER-AAAA</SqlEnlight_LicenseKey>
              <SqlEnlight_LicenseData>E:\SQL Enlight\license-activation-response.XML</SqlEnlight_LicenseData>
              </PropertyGroup>


              <Target Name="AnalyzeSqlScriptsTarget">

              <Message Text="Starting SQL script analysis..." />
              <Message Text="Running analysis on SQL script '$(SqlEnlight_AnalysisInputPath)'..." />

              <SqlCodeAnalyzeFilesTask
              FailOnRuleViolation="false"
              Quiet="true"
              Rules="$(SqlEnlight_AnalysisRules)"
              InputPath="$(SqlEnlight_AnalysisInputPath)"
              ReportOutputPath="$(SqlEnlight_AnalysisReportOutputPath)"
              ReportStylesheetPath="$(SqlEnlight_ReportStylesheetPath)"
              ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
              ContextFile="$(SqlEnlight_AnalysisContextFile)"
              ServerName="$(SqlEnlight_AnalysisServerName)"
              DatabaseName="$(SqlEnlight_AnalysisDatabaseName)"
              TemplateFile="$(SqlEnlight_AnalysisTemplateFile)"
              Verbosity="$(SqlEnlight_Verbosity)"
              MaxAge="$(SqlEnlight_MaxAge)"
              LicenseKey="$(SqlEnlight_LicenseKey)"
              LicenseData="$(SqlEnlight_LicenseData)">
              <Output TaskParameter="ViolationsCount" PropertyName="SqlEnlight_AnalysisViolationsCount" />
              <Output TaskParameter="ObjectsCount" PropertyName="SqlEnlight_AnalyzedObjectsCount" />

              </SqlCodeAnalyzeFilesTask>

              <Error Text="SQL Enlight analysis reported $(SqlEnlight_AnalysisViolationsCount) rule violations, see $(SqlEnlight_AnalysisReportOutputPath) for details."
              Condition="$(SqlEnlight_AnalysisViolationsCount) > 0" />

              <Message Text="Completed database script files analysis!" />
              </Target>

              </Project>

Related Topics

Integration with MSBild

SqlCodeAnalyzeServerTask

SqlCodeLayoutFilesTask

SqlCodeRefactorFilesTask