Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Generating osql Batch Scripts

Rate me:
Please Sign up or sign in to vote.
4.60/5 (10 votes)
8 Jan 2006CPOL3 min read 119.5K   4.1K   52   14
This article is on a tool I wrote to generate executable osql deployment batch scripts.

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:

C#
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 1st November, 2005.
  • Version 1.1 - Added sorting capabilities.

License

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


Written By
Web Developer
Netherlands Netherlands
I'm 27 years old and currently I'm working as a full time software developer/consultant in Amersfoort, Netherlands

Comments and Discussions

 
GeneralMaking sure scripts get installed in correct order Pin
Fiorina8110-Nov-05 2:43
Fiorina8110-Nov-05 2:43 
GeneralRe: Making sure scripts get installed in correct order Pin
Kant10-Nov-05 10:31
Kant10-Nov-05 10:31 
GeneralRe: Making sure scripts get installed in correct order Pin
Michael Erasmus10-Nov-05 18:29
Michael Erasmus10-Nov-05 18:29 
GeneralRe: Making sure scripts get installed in correct order Pin
Kant11-Nov-05 9:58
Kant11-Nov-05 9:58 
GeneralRe: Making sure scripts get installed in correct order Pin
Vertyg011-Nov-05 21:41
Vertyg011-Nov-05 21:41 
GeneralRe: Making sure scripts get installed in correct order Pin
scafrithuric25-Feb-10 8:35
scafrithuric25-Feb-10 8:35 

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.