Click or drag to resize

NAnt

Configure NAnt

Before using SQL Enlight tasks with NAnt a modification to NAnt’s configuration have to be made.

The following error may appear when a SQL Enlight task is executed:

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and can not be loaded in the 4.0 runtime without additional configuration information.

To resolve the error message and enable SQL Enlight tasks, an attribute useLegacyV2RuntimeActivationPolicy have to be added to the startup element in the NAnt.exe.config file.

 1<startup useLegacyV2RuntimeActivationPolicy="true">
 2<!-- .NET Framework 4.0 -->
 3<supportedRuntime
 4version="v4.0.30319" />
 5<!-- .NET Framework 2.0 -->
 6<supportedRuntime
 7version="v2.0.50727" />
 8<!-- .NET Framework 1.1 -->
 9<supportedRuntime
10version="v1.1.4322" />
11<!-- .NET Framework 1.0 -->
12<supportedRuntime
13version="v1.0.3705" />
14</startup>
sqlCodeAnalyzeFiles

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.
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 NAnt project sqlCodeAnalyzeFiles:

 1<?xml version="1.0" encoding="utf-8"?>
 2<project name="SQL Enlight Analyze SQL Files Example" default="runSqlEnlightAnalyzeScriptFiles" basedir="." >
 3<description>This is an example build file of using SQL Enlight's sqlCodeAnalyzeFiles task.</description>
 4
 5<loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" />
 6
 7<property name="SqlEnlight_AnalysisInputPath" value="E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql" />
 8<property name="SqlEnlight_AnalysisTemplateFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml" />
 9<property name="SqlEnlight_AnalysisRules" value="design,performance,ex0018" />
10<property name="SqlEnlight_AnalysisContextFile" value="E:\Projects\MyProject\Build\Context\AdventureWorks2008R2.xml" />
11<property name="SqlEnlight_AnalysisReportOutputPath" value="E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Script_AnalysisReport.html" />
12<property name="SqlEnlight_AnalysisReportStylesheetPath" value="C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt" />
13<property name="SqlEnlight_AnalysisRulesParametersFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml"/>
14<property name="SqlEnlight_AnalysisServerName" value="(local)"/>
15<property name="SqlEnlight_AnalysisDatabaseName" value="AdventureWorks2008R2"/>
16
17<!-- Windows Authentication Mode assumed when the username and password properties are empty. -->
18<property name="SqlEnlight_AnalysisUsername" value=""/>
19<property name="SqlEnlight_AnalysisPassword" value=""/>
20<property name="SqlEnlight_Verbosity" value="detail" />
21<property name="SqlEnlight_MaxAge" value="" />
22
23<target name="runSqlEnlightAnalyzeScriptFiles" description="Analyze database script files with SQL Enlight.">
24
25
26<echo message="${datetime::now()}: Starting database script files analysis..." />
27<echo message="Running analysis on SQL script ${SqlEnlight_AnalysisInputPath}..." />
28
29<sqlCodeAnalyzeFiles failOnRuleViolation="false"
30quiet="false"
31inputPath="${SqlEnlight_AnalysisInputPath}"
32templateFile="${SqlEnlight_AnalysisTemplateFile}"
33rules="${SqlEnlight_AnalysisRules}"
34reportOutputPath="${SqlEnlight_AnalysisReportOutputPath}"
35reportStylesheetPath="${SqlEnlight_AnalysisReportStylesheetPath}"
36contextFile="${SqlEnlight_AnalysisContextFile}"
37serverName="${SqlEnlight_AnalysisServerName}"
38databaseName="${SqlEnlight_AnalysisDatabaseName}"
39parametersFile="${SqlEnlight_AnalysisRulesParametersFile}"
40verbosity="${SqlEnlight_Verbosity}"
41maxAge="${SqlEnlight_MaxAge}"
42/>
43<xmlpeek
44file="${SqlEnlight_AnalysisReportOutputPath}"
45xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='IssuesCount']/@Value"
46property="SqlEnlight_AnalysisViolationsCount"
47failonerror="false">
48</xmlpeek>
49
50<xmlpeek
51file="${SqlEnlight_AnalysisReportOutputPath}"
52xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='ObjectsCount']/@Value"
53property="SqlEnlight_AnalyzedObjectsCount"
54failonerror="false">
55</xmlpeek>
56
57<fail if="${int::parse(SqlEnlight_AnalysisViolationsCount) > 0}" verbose="true" message="SQL Enlight analysis reported ${SqlEnlight_AnalysisViolationsCount} rule violations. See ${SqlEnlight_AnalysisReportOutputPath} for details."/>
58
59<echo message="${datetime::now()}: Completed database script files analysis!" />
60
61</target>
62</project>

sqlCodeAnalyzeServer

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.

reportOutputPath

Output location of the analysis XML report.

Example: "C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt"

reportStylesheetPath XSLT document that to be used to transform the report XML.

Example: "C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt"

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.

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.

Example

Example NAnt project sqlCodeAnalyzeServer:

 1<?xml version="1.0" encoding="utf-8"?>
 2<project name="SQL Enlight Analyze SQL Server Example" default="runSqlCodeAnalyzeServer" basedir=".">
 3<description>This is an example build file of using SQL Enlight's sqlCodeAnalyzeServer task.</description>
 4
 5
 6<loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" />
 7
 8<property name="SqlEnlight_AnalysisTemplateFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml" />
 9<property name="SqlEnlight_AnalysisRules" value="design,performance,ex0018" />
10<property name="SqlEnlight_AnalysisReportOutputPath" value="E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Database_AnalysisReport.html" />
11<property name="SqlEnlight_AnalysisReportStylesheetPath" value="C:\Program Files\Ubitsoft\SQL Enlight\Xslt\SqlEnlightReport.xslt" />
12<property name="SqlEnlight_AnalysisRulesParametersFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml"/>
13<property name="SqlEnlight_AnalysisServerName" value="(local)"/>
14<property name="SqlEnlight_AnalysisDatabaseName" value="AdventureWorks2008R2"/>
15<property name="SqlEnlight_AnalysisViolationsCount" value="0" />
16<property name="SqlEnlight_AnalyzedObjectsCount" value="0" />
17
18<!-- Windows Authentication Mode assumed when the username and password properties are empty. -->
19<property name="SqlEnlight_AnalysisUsername" value=""/>
20<property name="SqlEnlight_AnalysisPassword" value=""/>
21
22<property name="SqlEnlight_Verbosity" value="normal" />
23<property name="SqlEnlight_MaxAge" value="" />
24
25<target name="runSqlCodeAnalyzeServer" description="Analyze database with SQL Enlight.">
26
27<echo message="${datetime::now()}: Starting database analysis..." />
28<echo message="Running analysis on SQL Server '${SqlEnlight_AnalysisServerName}' database '${SqlEnlight_AnalysisDatabaseName}'..." />
29
30<sqlCodeAnalyzeServer failOnRuleViolation="false"
31quiet="false"
32serverName="${SqlEnlight_AnalysisServerName}"
33databaseName="${SqlEnlight_AnalysisDatabaseName}"
34userName="${SqlEnlight_AnalysisUsername}"
35password="${SqlEnlight_AnalysisPassword}"
36objectType="database"
37objectName="${SqlEnlight_AnalysisDatabaseName}"
38templateFile="${SqlEnlight_AnalysisTemplateFile}"
39rules="${SqlEnlight_AnalysisRules}"
40reportOutputPath="${SqlEnlight_AnalysisReportOutputPath}"
41reportStylesheetPath="${SqlEnlight_AnalysisReportStylesheetPath}"
42parametersFile="${SqlEnlight_AnalysisRulesParametersFile}"
43verbosity="${SqlEnlight_Verbosity}"
44maxAge="${SqlEnlight_MaxAge}"
45/>
46<xmlpeek
47file="${SqlEnlight_AnalysisReportOutputPath}"
48xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='IssuesCount']/@Value"
49property="SqlEnlight_AnalysisViolationsCount"
50failonerror="false">
51</xmlpeek>
52
53<xmlpeek
54file="${SqlEnlight_AnalysisReportOutputPath}"
55xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='ObjectsCount']/@Value"
56property="SqlEnlight_AnalyzedObjectsCount"
57failonerror="false">
58</xmlpeek>
59
60<fail if="${int::parse(SqlEnlight_AnalysisViolationsCount) > 0}" verbose="true" message="SQL Enlight analysis reported ${SqlEnlight_AnalysisViolationsCount} rule violations. See ${SqlEnlight_AnalysisReportOutputPath} for details."/>
61
62<echo message="${datetime::now()}: Completed database analysis!" />
63
64</target>
65</project>

sqlCodeLayoutFiles

The task reformats T-SQL script files using given layout template.

Parameters

Name

Description

quiet

Suppresses all except error output messages.

inputPath

Required. Source T-SQL 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" .

outputPath

Required. Output location for the reformatted T-SQL script files.

Example: "c:\my files\result\" .

templateName

Exact name of the layout template or external layout template file which to be used. The default layout template will be used in case this argument is not provided.

Example: "My Favorite Layout Template"

Or

"D:\My Layout Templates\My Favorite Layout Template.layouttemplate"

includeSubfolders Include files in sub folders.

Example

Example NAnt project using sqlCodeLayoutFiles:

 1<?xml version="1.0" encoding="utf-8"?>
 2<project name="SQL Enlight Layout SQL Script Files Example" default="runSqlCodeLayoutFiles" basedir=".">
 3<description>This is an example build file of using SQL Enlight's sqlCodeLayoutFiles task.</description>
 4<loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" />
 5
 6<property name="SqlEnlight_LayoutTemplate" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Layout\Ubitsoft Favorite 1.layouttemplate" />
 7<property name="SqlEnlight_LayoutInputPath" value="E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql" />
 8<property name="SqlEnlight_LayoutOutputPath" value="E:\Projects\MyProject\SQL Script\Reformatted\" />
 9
10<target name="runSqlCodeLayoutFiles" description="Layout script files SQL Enlight.">
11
12<echo message="${datetime::now()}: Starting script layout..." />
13<echo message="Applying '${SqlEnlight_LayoutTemplate}' layout template to the '${SqlEnlight_LayoutInputPath}' files ..." />
14
15<sqlCodeLayoutFiles quiet="true"
16inputPath="${SqlEnlight_LayoutInputPath}"
17outputPath="${SqlEnlight_LayoutOutputPath}"
18templateName="${SqlEnlight_LayoutTemplate}"
19verbosity="${SqlEnlight_Verbosity}"
20/>
21
22<echo message="${datetime::now()}: Completed script layout!" />
23
24</target>
25</project>
sqlCodeRefactorFiles

The task tuns code refactorings on T-SQL script files.

Parameters

Name

Description

refactorings

A list of code names of the refactorings which to be applied. Items can be separated with ',',';' or '|'.

Example: RE0001,RE0002,RE0006

inputPath

Required. Source T-SQL 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" .

outputPath

Required. Output location for the reformatted T-SQL script files.

Example: "c:\my files\result\" .

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.

objectsCount Output parameter returning the total number of processed objects.

Example

Example NAnt project sqlCodeRefactorFiles:

 1<?xml version="1.0" encoding="utf-8"?>
 2<project name="SQL Enlight Refactor SQL Script Files Example" default="runSqlCodeRefactorFiles" basedir=".">
 3<description>This is an example build file of using SQL Enlight's runSqlCodeRefactorFiles task.</description>
 4<loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" />
 5
 6<property name="SqlEnlight_Refactorings" value="RE0001, RE0002, RE0003" />
 7<property name="SqlEnlight_InputPath" value="E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql" />
 8<property name="SqlEnlight_OutputPath" value="E:\Projects\MyProject\SQL Script\Refactored\" />
 9
10<target name="runSqlCodeRefactorFiles" description="Refactor script files SQL Enlight.">
11
12<echo message="${datetime::now()}: Starting code refactoring..." />
13<echo message="Applying '${SqlEnlight_Refactorings}' code refactorings to the '${SqlEnlight_InputPath}' files ..." />
14
15<sqlCodeRefactorFiles
16inputPath="${SqlEnlight_InputPath}"
17outputPath="${SqlEnlight_OutputPath}"
18refactorings="${SqlEnlight_Refactorings}"
19/>
20
21<echo message="${datetime::now()}: Completed code refactoring!" />
22
23</target>
24</project>
See Also