Click here to Skip to main content
Click here to Skip to main content

MS SQL Server script validation with NAnt Task

, 31 Aug 2006
Rate this:
Please Sign up or sign in to vote.
Validate MS SQL Server scripts with NAnt to improve continuous integration.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Stefan Fork
Software Developer
Germany Germany
No Biography provided

Comments and Discussions

 
GeneralCreating DB in build script PinmemberIgor Brejc31-Aug-06 19:49 
AnswerRe: Creating DB in build script PinmemberStefan Fork20-Sep-06 20:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 31 Aug 2006
Article Copyright 2006 by Stefan Fork
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid