Click here to Skip to main content
15,117,779 members
Articles / Database Development / SQL Server
Posted 31 Aug 2006


21 bookmarked

MS SQL Server script validation with NAnt Task

Rate me:
Please Sign up or sign in to vote.
3.92/5 (7 votes)
31 Aug 20062 min read
Validate MS SQL Server scripts with NAnt to improve continuous integration.


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.


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

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"/›



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.


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 Pin
Igor Brejc31-Aug-06 20:49
MemberIgor Brejc31-Aug-06 20:49 
AnswerRe: Creating DB in build script Pin
Stefan Fork20-Sep-06 21:49
MemberStefan Fork20-Sep-06 21:49 

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

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