Click here to Skip to main content
15,904,153 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

MS SQL Scripts Runner

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
4 Apr 2012CPOL2 min read 41.8K   9   13
Script Runner that can run multiple SQL script files on MS-SQL.

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.

C#
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:

C#
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

Image 1

Image 2

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

This article was originally posted at http://scriptzrunner.codeplex.com

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Singapore Singapore
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAlready been done... Pin
pt14016-Apr-12 22:23
pt14016-Apr-12 22:23 
AnswerRe: Already been done... Pin
sluaghtered6-Apr-12 22:42
sluaghtered6-Apr-12 22:42 
GeneralRe: Already been done... Pin
pt14016-Apr-12 23:10
pt14016-Apr-12 23:10 
GeneralRe: Already been done... Pin
sluaghtered6-Apr-12 23:38
sluaghtered6-Apr-12 23:38 
GeneralRe: Already been done... Pin
pt14017-Apr-12 0:04
pt14017-Apr-12 0:04 
GeneralRe: Already been done... Pin
sluaghtered7-Apr-12 0:10
sluaghtered7-Apr-12 0:10 
GeneralRe: Already been done... Pin
pt14017-Apr-12 0:54
pt14017-Apr-12 0:54 
GeneralRe: Already been done... Pin
sluaghtered7-Apr-12 1:00
sluaghtered7-Apr-12 1:00 
GeneralRe: Already been done... Pin
pt14017-Apr-12 1:06
pt14017-Apr-12 1:06 
GeneralRe: Already been done... Pin
pt14017-Apr-12 2:14
pt14017-Apr-12 2:14 
QuestionAwesome Pin
Dirk_Strauss4-Apr-12 21:15
professionalDirk_Strauss4-Apr-12 21:15 
Hey, I'm just happy to have this tool Smile | :)
AnswerRe: Awesome Pin
sluaghtered4-Apr-12 21:28
sluaghtered4-Apr-12 21:28 
GeneralThoughts Pin
PIEBALDconsult4-Apr-12 8:08
mvePIEBALDconsult4-Apr-12 8:08 

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.