Generating osql Batch Scripts

• Download demo project - 8.77 Kb
• Download source - 10.5 Kb

Introduction

This is a small tool I wrote that will scan a selected folder for all the .sql files and generate a MS-DOS batch script that will execute the scripts on a selected server/database.

Background

In my work, I often find myself writing an assortment of stored procedures, user defined functions and views that are executed on the development server. I usually save all my SQL scripts in text files with a .sql extension. After development these files have to be deployed to the production server. I have found the osql utility that comes installed with SQL Server to be very handy in these situations. All I have to do is create a batch script that has to be copied with the SQL scripts. This batch script can then be executed to run all the scripts on a specified server.

But creating these batch scripts manually can be a bit of a pain. That's why I wrote this tool.

Using the code

The projects consists of a GUI that uses the OsqlScript class to generate the file. This class has the following properties:

• string Folder - The path of the folder to scan.
• string BatchFileName - The name and path of the batch file to be generated (without the ".bat").
• string Server - The SQL Server instance used.
• string Database - The database to use.
• bool UseIntegratedSecurity - Specifies to use Windows authentication to log in.
• string Username and string Password
• bool UseReportFile - Specifies that the generated script will log to a report file.
• string Reportfile - The file name and extension to use as a report file.

Once you have created an instance of the class, set its properties to the appropriate values.

To generate the file just call OsqlScript.Generate().

Here is an example:

OsqlScript oscript = new OsqlScript("C:\\scripts",
                          "C:\\scripts\\ExecuteScripts");
oscript.Server = "MyServer";
oscript.Database = "Northwind";
oscript.UseIntegratedSecurity = true;
oscript.UseReportFile = true;
oscript.ReportFile = "Report.txt";

//generate the file
oscript.Generate();

The object will generate the specified batch script to run the osql command line utility for all the .sql files in the folder. Just execute the batch script in the same directory as the SQL scripts to execute them.

Sorting additions

Version 1.1 includes sorting capabilities. The script execution sort order can be either done alphabetically (default), or based on the date created, date last modified, or the file name date (the object will try to parse the date from the filename, e.g. '2002/07/09.sql'). To do the sorting, I called the Array.Sort method and created an IComparer class for each sorting method. (Which by the way, I think just rocks, custom sorting is so easy in .NET!). So to create any other sorting method is as easy as creating an ICompare object and implementing the Compare method.

Conclusion

This is a very simple but handy deployment tool that I use and I hope that someone else would find it useful. Any improvements, bug fixes or suggestions are more than welcome!

History

• Version 1 - Submitted on 1^st November, 2005.
• Version 1.1 - Added sorting capabilities.