How to easily automate SQL Code Guard.

It may happen that you have a need to check the scripts that you have on your disk.  For example, it may be part of your build process.

So how you can run SQL Code Guard as a command-line tool?

If you are a TRUE programmer then the answer is obvious: you DEFINITELY MUST write your own application. And yes, you can do it! Especially for you SQL Code Guard provides nice API (you read here how to use it).

But if you are the wise and busy DBA then you need quick and simple answer.

And yet again - SQL Code Guard provides you with nice and quick answer: you can use msbuild.

You must perform two simple steps:

1st. You should read simple help on SQL Code Guard msbuild attributes here.

2nd. You should write simple msbuild file (sample file you can find at SQL Code Guard installation folder; it named SampleProject.msbuild).

Your file can look like this:

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
   <UsingTask AssemblyFile="SQLCodeGuard.MSBuild.dll" TaskName="SQLCodeGuard.MSBuild.CheckTSQL" />
  
   <PropertyGroup>
      <OutputPath>c:\temp</OutputPath>
   </PropertyGroup>

   <Target Name="Release">
     <CheckTSQL SourcePath="d:\Projects\SqlScripts" IncludeIssue="ALL;CGUNP" ExcludeIssue ="DEP023" OutFile="result.xml" Quiet ="true"/>
   </Target>
</Project>

After these two steps you can run msbuild like  that:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe CheckMySqlScripts.msbuild 

Now you can relax and wait (for not so long - SQL Code Guard is pretty fast beast!)  while execution completes.
After execution you can inspect out file result.xml

<root>
    <file fullname="d:\Projects\SqlScripts\dbo.uspGetBillOfMaterials.StoredProcedure.sql" name="dbo.uspGetBillOfMaterials.StoredProcedure.sql">
        <issue code="DEP013" line="1" column="5" text="Deprecated SET options" message="ANSI_NULLS"/>
        <issue code="MI003" line="26" column="133" text="Unqualified column name" message="RecursionLevel"/>
    </file>
</root>

It's simple, isn't it?