MS SQL Scripts Runner

• SQL Script Runner @ CodePlex

Introduction

This is a small executable to run multiple SQL script files on MS-SQL Server.

One of the biggest advantages with our script runner is that faster than the DB client library. We are using SMO library for execution, we managed to compile it for .NET 4. This is the only script runner compiled for .Net 4. We have a large statement timeout but can abort at any time during the execution. We have found a way to embedded an excel resource so that we can produce reports.

Background

For years, I always found it useful to have an easy way to update my SQL Server databases, this application addresses the issue; the GUI application can be used to test scripts and produce a SQL Server Runner project that can be used by the console version. Development teams that need an automated way to update their databases may find that the console version fulfills their requirements.

The biggest problem was making the application asynchronous. This enables the application to run a report or cancel the operation at any time during execution of scripts. By using a back ground worker this enabled the GUI to still function.

We used a back ground worker like this in the code example.

static void Main(string[] args) 
{
   BackgroundWorker worker = new BackgroundWorker();
   //DoWork is a delegate, where you can add tasks
   worker.DoWork += (sender, e) =>
   {
       //run sql statement block
   };
   worker.RunWorkerCompleted += (sender, e) =>
   {
       var IfYouWantTheResult = e.Result;
       //pass sql script
   };
   worker.RunWorkerAsync();
   //you can cancel the worker/report its progress by its methods.
}

Also the problem was pushing out the logs during execution of the thread which was done using:

worker.WorkerReportsProgress = true;
worker.ProgressChanged += new ProgressChangedEventHandler(worker_ProgressChanged);

Features Overview

• Simple easy to use GUI design.
• Drag And Drop script files in any order.
• Run a directory of script files.
• SQL script output messages during execution
• Script passed or failed that are colored green and red (yellow for running)
• Stop on error option
• Open script on error option
• Run report in to Excel with time taken for each script
• Total duration time
• Test DB connection
• Asynchronous by using threads
• .NET 4 and tested with SQL 2008
• Single exe file; no installation is required. The only single file script runner
• Kill connection at any time

Screenshots

Source code and application can be downloaded from Microsoft CodePlex from here: https://scriptzrunner.codeplex.com/.