MS SQL Server script validation with NAnt Task

• Download ScriptValidatorTask source - 130 Kb

Introduction

The ScriptValidatorTask is a NAnt task to validate Microsoft SQL Server scripts like procedures, functions, simple statements, and views. It uses the NOEXEC option of the SQL Server to validate the scripts.

Background

Automating the build process is a crucial part of Continuous Integration; once start, you will never want to go back. You receive the build result by running a NAnt script, and every developer involved in the project has to run it before committing his changes to a source repository. The NUnit tests could check business logic as well as technical aspects. For the sake of automation, a build server (like CruiseControl.NET) is essential - every time a developer commits changes to the repository, the listening build server starts a build process and notifies the developers.

Unfortunately, SQL scripts like procedures and views are not validated in this process. Normal code would be compiled, but SQL scripts have to be checked against the database. Thus, the idea of a SQL script validating a NAnt task was born.

I will not describe in detail how to build NAnt tasks or how scripts are validated, but you will find deeper knowledge about this in the community. My intention was to put these things together to use them in an automated build environment to comply with the concepts of Continuous Integration. The validated scripts are limited to MS SQL Server.

Using the code

First of all, you have to announce the task to NAnt with <loadtasks>.

...
‹loadtasks assembly=".\lib\NAnt.ScriptValidatorTasks.dll" /›
...

Then you can use the new task scriptvalidate in the build script.

...
‹scriptvalidate connection="data source=SQL Server;initial
        catalog=Database;user=username;password=pwd;"›
...

To validate the scripts, a connection to the database server is necessary. The argument connection represents the connection string.

A FileSet scripts defines the script-files to validate.

...

    <scripts basedir=".\"›

    <include name="*.sql"/›

    <include name="*.prc"/›

    <exclude name="SPTest.prc"/›

    </scripts›

...

Now, the validation of SQL Server scripts are possible within the process of a NAnt build.

Points of interest

My SQL Server scripts contain many GO statements. Each stored procedure script drops the procedure in the database if it exists and then creates it. This must happen because T-SQL does not support CREATE OR REPLACE like Oracle does. ADO.NET does not support GO statements in the CommandText, so I had to split each CommandText in many single CommandText each time a GO statement occurs.

Unfortunately, the compiler of the SQL Server is not very restrictive. When a stored procedure selects from a table that does not exist in the database, no error or warning is thrown. The only workaround is to write your own SQL parser and validator.