SQL Server Runner - Part 1

SQL SERVER RUNNER

Introduction

SQL Server Runner is a .NET application that allows users to execute SQL scripts against SQL Server databases. Its intuitive GUI makes the task of running scripts an easy one; it is an indispensable tool for those DBAs/Developers that need to work with SQL Server.

Two minor versions were created since this article was written. In the latest version (2.0.1.2), a placeholder feature allows to write SQL scripts using variables; when a script is executed, the variable is automatically replaced by the value stored at the application configuration file.

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; NAnt can be used to call the application, and the logging level can be modified to provide different types of information as the application uses log4Net.

Features overview

General features

• Executes multiple scripts in one single execution.
• Full error message is reported if script fails.
• The connection manager allows the user to indicate how the connection behaves during the execution of the scripts, for example:
• All scripts are executed in one single transaction, if a script fails the transaction is rolled back.
• Scripts execute in a transaction that is rolled back at the end of the transaction. This is useful when testing scripts.
• log4Net is used to report and audit the execution process. The default logger uses a log file, but this could be changed to use email, SQL Server tables, or even the Windows application log.
• Scripts can be written using variables so the application replaces them when they are executed.

GUI application

• The GUI application helps to create projects that can be executed using the command line application.
• Individual folder flags to indicate if subfolders must be executed.
• Tree view window that reports which folders and scripts were executed.
• SQL native error message window to report individual script failures.
• A double click in a script in the tree view window opens the script in the default SQL Server script editor.
• A color scheme is used in the script window to easily identify folders/scripts that have failed.
• A "cancel" function is available so the execution can be stopped.
• The counter section returns the total number of scripts executed, the number of scripts that failed, and the total time that the scripts took to execute.
• A progress bar indicator is displayed during the execution of the scripts.

Console application

• Executes SQL Server Runner projects directly from the command line.
• Error indicator parameter indicates if the application should return an error if one or more scripts fail. It can also be used to indicate if the execution must stop if a script fails.
• The console application was enhanced in version 2.0.1.0, so the connection managers are also available from the command line.

Screenshot

Getting the application

Please have a look at the documentation that you can find at the SQLRunner website which is located within the Microsoft Open Source web at CodePlex. The manual/tutorial describes all the application features in detail, and it can also be used as a guideline to find out how to use the application.

Relevant links

• SQL Server Runner - log4Net example