• MSBuild Integration
• NAnt Integration

MSBuild Integration

• SqlCodeAnalyzeFilesTask
• SqlCodeAnalyzeServerTask
• SqlCodeLayoutFilesTask

SqlCodeAnalyzeFilesTask

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

Task parameters:

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_AnalysisRules>ex0018,Design,Performance</SqlEnlight_AnalysisRules>
      <SqlEnlight_AnalysisReportOutputPath>E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Script_AnalysisReport.xml</SqlEnlight_AnalysisReportOutputPath>
      <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>
    </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)"
                    ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
                    ContextFile="$(SqlEnlight_AnalysisContextFile)"
                    TemplateFile="$(SqlEnlight_AnalysisTemplateFile)">                        
            <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>

SqlCodeAnalyzeServerTask

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

Task parameters:

Example MSBuild project SqlCodeAnalyzeServerTask:

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

  <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeServerTask"
             AssemblyFile="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
             
  <PropertyGroup>
    <SqlEnlight_AnalysisServerName>.</SqlEnlight_AnalysisServerName>
    <SqlEnlight_AnalysisDatabaseName>AdventureWorks2008R2</SqlEnlight_AnalysisDatabaseName>
    <SqlEnlight_AnalysisUsername></SqlEnlight_AnalysisUsername>
    <SqlEnlight_AnalysisPassword></SqlEnlight_AnalysisPassword>
    <SqlEnlight_AnalysisObjectType>database</SqlEnlight_AnalysisObjectType>
    <SqlEnlight_AnalysisObjectName>AdventureWorks2008R2</SqlEnlight_AnalysisObjectName>
    <SqlEnlight_AnalysisRules>ex0018,Design,Performance</SqlEnlight_AnalysisRules>
    <SqlEnlight_AnalysisReportOutputPath>E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Database_AnalysisReport.xml</SqlEnlight_AnalysisReportOutputPath>
    <SqlEnlight_AnalysisRulesParametersFile>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml</SqlEnlight_AnalysisRulesParametersFile>
    <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>
      
  </PropertyGroup>

    <Target Name="AnalyzeDatabaseTarget">
  
    <Message Text="Starting database analysis..." />
    <Message Text="Running analysis on SQL Server '$(SqlEnlight_AnalysisServerName)' database '$(SqlEnlight_AnalysisDatabaseName)'..." />

    <SqlCodeAnalyzeServerTask
                    FailOnRuleViolation="false"
                    Quiet="true"
                    ServerName="$(SqlEnlight_AnalysisServerName)"
                    DatabaseName="$(SqlEnlight_AnalysisDatabaseName)"
                    ObjectType="$(SqlEnlight_AnalysisObjectType)"
                    ObjectName="$(SqlEnlight_AnalysisObjectName)"
                    Rules="$(SqlEnlight_AnalysisRules)"
                    ReportOutputPath="$(SqlEnlight_AnalysisReportOutputPath)"
                    ParametersFile="$(SqlEnlight_AnalysisRulesParametersFile)"
                    TemplateFile="$(SqlEnlight_AnalysisTemplateFile)">                  
            <Output TaskParameter="ViolationsCount" PropertyName="SqlEnlight_AnalysisViolationsCount" />
            <Output TaskParameter="ObjectsCount" PropertyName="SqlEnlight_AnalyzedObjectsCount" />
      </SqlCodeAnalyzeServerTask>
     
    <Error Text="SQL Enlight analysis reported $(SqlEnlight_AnalysisViolationsCount) rule violations, see $(SqlEnlight_AnalysisReportOutputPath) for details."
           Condition="$(SqlEnlight_AnalysisViolationsCount) > 0" />

    <Message Text="Completed database analysis!" />

  </Target>

  

</Project>

SqlCodeLayoutFilesTask

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

Task parameters:

Example MSBuild project using SqlCodeLayoutFilesTask:

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

  <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeLayoutFilesTask"
             AssemblyFile="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
             
  <PropertyGroup>
    <SqlEnlight_LayoutInputPath>E:\Projects\MyProject\SQL Script\*.sql</SqlEnlight_LayoutInputPath>
    <SqlEnlight_LayoutOutputPath>E:\Projects\MyProject\SQL Script\Reformatted\</SqlEnlight_LayoutOutputPath>
    <SqlEnlight_LayoutTemplate>C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Layout\Ubitsoft Favorite 1.layouttemplate</SqlEnlight_LayoutTemplate>
  </PropertyGroup>

  <Target Name="LayoutScriptsTarget">
  
    <Message Text="Starting SQL script layout..." />
    <Message Text="Running SQL script layout '$(SqlEnlight_LayoutInputPath)'..." />
    
    <SqlCodeLayoutFilesTask ContinueOnError="false"    
                            TemplateName="$(SqlEnlight_LayoutTemplate)"
                            InputPath="$(SqlEnlight_LayoutInputPath)"
                            OutputPath="$(SqlEnlight_LayoutOutputPath)" />
                              
    <Message Text="Completed SQL script layout!" />
  </Target>
</Project>

        

NAnt Integration

• sqlCodeAnalyzeFiles
• sqlCodeAnalyzeServer
• sqlCodeLayoutFiles

Before using SQL Enlight tasks with NAnt a modification to NAnt�s configuration have to be made. The change is required , because 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.

sqlCodeAnalyzeFiles

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

Task parameters:

Example NAnt project sqlCodeAnalyzeFiles:

<?xml version="1.0" encoding="utf-8"?>
<project name="SQL Enlight Analyze SQL Files Example" default="runSqlEnlightAnalyzeScriptFiles" basedir="." >
  <description>This is an example build file of using SQL Enlight's sqlCodeAnalyzeFiles task.</description>  

  <loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" /> 
  
  <property name="SqlEnlight_AnalysisInputPath" value="E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql" />
  <property name="SqlEnlight_AnalysisTemplateFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml" />
  <property name="SqlEnlight_AnalysisRules" value="design,performance,ex0018" />
  <property name="SqlEnlight_AnalysisContextFile" value="E:\Projects\MyProject\Build\Context\AdventureWorks2008R2.xml" />
  <property name="SqlEnlight_AnalysisReportOutputPath" value="E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Script_AnalysisReport.xml" />  
  <property name="SqlEnlight_AnalysisRulesParametersFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml"/>
  
  <target name="runSqlEnlightAnalyzeScriptFiles" description="Analyze database script files with SQL Enlight.">

    <echo message="${datetime::now()}: Starting database script files analysis..." />
    <echo message="Running analysis on SQL script ${SqlEnlight_AnalysisInputPath}..." />

    <sqlCodeAnalyzeFiles failOnRuleViolation="false"
                         quiet="false"
                         inputPath="${SqlEnlight_AnalysisInputPath}"
                         templateFile="${SqlEnlight_AnalysisContextFile}"
                         rules="${SqlEnlight_AnalysisRules}"                         
                         reportOutputPath="${SqlEnlight_AnalysisReportOutputPath}"
                         contextFile="${SqlEnlight_AnalysisContextFile}"
                         parametersFile="${SqlEnlight_AnalysisRulesParametersFile}"
                        />
      <xmlpeek
        file="${SqlEnlight_AnalysisReportOutputPath}"
        xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='IssuesCount']/@Value"
        property="SqlEnlight_AnalysisViolationsCount"
        failonerror="false">
      </xmlpeek>

      <xmlpeek
        file="${SqlEnlight_AnalysisReportOutputPath}"
        xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='ObjectsCount']/@Value"
        property="SqlEnlight_AnalyzedObjectsCount"
        failonerror="false">
      </xmlpeek>    

      <fail if="${int::parse(SqlEnlight_AnalysisViolationsCount) > 0}" verbose="true" message="SQL Enlight analysis reported ${SqlEnlight_AnalysisViolationsCount} rule violations. See ${SqlEnlight_AnalysisReportOutputPath} for details."/>

      <echo message="${datetime::now()}: Completed database script files analysis!" />

    </target>
</project>

sqlCodeAnalyzeServer

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

Task parameters:

Example NAnt project sqlCodeAnalyzeServer:

<?xml version="1.0" encoding="utf-8"?>
<project name="SQL Enlight Analyze SQL Server Example" default="runSqlCodeAnalyzeServer" basedir=".">
  <description>This is an example build file of using SQL Enlight's sqlCodeAnalyzeServer task.</description>
  

  <loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" /> 

  <property name="SqlEnlight_AnalysisTemplateFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Analysis\DefaultAnalysisTemplate.xml" />
  <property name="SqlEnlight_AnalysisRules" value="design,performance,ex0018" />
  <property name="SqlEnlight_AnalysisReportOutputPath" value="E:\Projects\MyProject\Build\Reports\AdventureWorks2008R2_Database_AnalysisReport.xml" />  
  <property name="SqlEnlight_AnalysisRulesParametersFile" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Xml\AnalysisParameters.xml"/>
  <property name="SqlEnlight_AnalysisServerName" value="(local)"/>
  <property name="SqlEnlight_AnalysisDatabaseName" value="AdventureWorks2008R2"/>
  <property name="SqlEnlight_AnalysisViolationsCount" value="0" />
  <property name="SqlEnlight_AnalyzedObjectsCount" value="0" />
  
  <!-- Windows Authentication Mode assumed when the username and password properties are empty. -->
  <property name="SqlEnlight_AnalysisUsername" value=""/>
  <property name="SqlEnlight_AnalysisPassword" value=""/>
<!--                            
-->                          
  <target name="runSqlCodeAnalyzeServer" description="Analyze database with SQL Enlight.">

    <echo message="${datetime::now()}: Starting database analysis..." />
    <echo message="Running analysis on SQL Server '${SqlEnlight_AnalysisServerName}' database '${SqlEnlight_AnalysisDatabaseName}'..." />
    
    <sqlCodeAnalyzeServer failOnRuleViolation="false"
                          quiet="false"
                          serverName="${SqlEnlight_AnalysisServerName}"
                          databaseName="${SqlEnlight_AnalysisDatabaseName}"
                          userName="${SqlEnlight_AnalysisUsername}"
                          password="${SqlEnlight_AnalysisPassword}"
                          objectType="database"
                          objectName="${SqlEnlight_AnalysisDatabaseName}"
                          templateFile="${SqlEnlight_AnalysisTemplateFile}"
                          rules="${SqlEnlight_AnalysisRules}"                          
                          reportOutputPath="${SqlEnlight_AnalysisReportOutputPath}"
                          parametersFile="${SqlEnlight_AnalysisRulesParametersFile}"                         
                        />
      <xmlpeek
        file="${SqlEnlight_AnalysisReportOutputPath}"
        xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='IssuesCount']/@Value"
        property="SqlEnlight_AnalysisViolationsCount"
        failonerror="false">
      </xmlpeek>

      <xmlpeek
        file="${SqlEnlight_AnalysisReportOutputPath}"
        xpath="/SqlEnlightReport/ExtendedProperties/ExtendedProperty[@Name='ObjectsCount']/@Value"
        property="SqlEnlight_AnalyzedObjectsCount"
        failonerror="false">
      </xmlpeek>    

      <fail if="${int::parse(SqlEnlight_AnalysisViolationsCount) > 0}" verbose="true" message="SQL Enlight analysis reported ${SqlEnlight_AnalysisViolationsCount} rule violations. See ${SqlEnlight_AnalysisReportOutputPath} for details."/>
      
      <echo message="${datetime::now()}: Completed database analysis!" />

    </target>
</project>

sqlCodeLayoutFiles

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

Task parameters:

Example NAnt project using sqlCodeLayoutFiles:

<?xml version="1.0" encoding="utf-8"?>
<project name="SQL Enlight Layout SQL Script Files Example" default="runSqlCodeLayoutFiles" basedir=".">
  <description>This is an example build file of using SQL Enlight's sqlCodeLayoutFiles task.</description>
  <loadtasks assembly="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.NAnt.dll" /> 

  <property name="SqlEnlight_LayoutTemplate" value="C:\Program Files (x86)\Ubitsoft\SQL Enlight\Templates\Layout\Ubitsoft Favorite 1.layouttemplate" />
  <property name="SqlEnlight_LayoutInputPath" value="E:\Projects\MyProject\SQL Script\AdventureWorks2008R2.sql" />
  <property name="SqlEnlight_LayoutOutputPath" value="E:\Projects\MyProject\SQL Script\Reformatted\" />  

  <target name="runSqlCodeLayoutFiles" description="Layout script files SQL Enlight.">

    <echo message="${datetime::now()}: Starting script layout..." />
    <echo message="Applying '${SqlEnlight_LayoutTemplate}' layout template to the '${SqlEnlight_LayoutInputPath}' files ..." />

    <sqlCodeLayoutFiles quiet="true"
                        inputPath="${SqlEnlight_LayoutInputPath}"
                        outputPath="${SqlEnlight_LayoutOutputPath}"
                        layoutTemplate="${SqlEnlight_LayoutTemplate}"                         
                        />
                        
      <echo message="${datetime::now()}: Completed script layout!" />

    </target>
</project>

        

---