Click here to Skip to main content
Click here to Skip to main content
Go to top

Generating osql Batch Scripts

, 8 Jan 2006
Rate this:
Please Sign up or sign in to vote.
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:

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)

Share

About the Author

Michael Erasmus
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

 
Questionwhat is default server name Pinmembersankalp sawant25-Feb-13 20:37 
GeneralAnother option PinmemberJeffPClark19-Nov-05 3:21 
GeneralBetter and easy way PinmemberTareq Muhammad18-Nov-05 23:36 
GeneralRe: Better and easy way PinmemberMichael Erasmus4-Jan-06 20:07 
GeneralRe: Better and easy way Pinmembercjohnson617-Apr-06 10:45 
GeneralRe: Better and easy way PinmemberMichael Erasmus20-Jun-06 20:48 
Generalsa Pinmemberzoyo14-Nov-05 16:55 
GeneralCool PinmemberKant10-Nov-05 10:25 
GeneralMaking sure scripts get installed in correct order PinmemberFiorina8110-Nov-05 2:43 
GeneralRe: Making sure scripts get installed in correct order PinmemberKant10-Nov-05 10:31 
GeneralRe: Making sure scripts get installed in correct order PinmemberMichael Erasmus10-Nov-05 18:29 
GeneralRe: Making sure scripts get installed in correct order PinmemberKant11-Nov-05 9:58 
GeneralRe: Making sure scripts get installed in correct order PinmemberVertyg011-Nov-05 21:41 
GeneralRe: Making sure scripts get installed in correct order Pinmemberscafrithuric25-Feb-10 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 8 Jan 2006
Article Copyright 2005 by Michael Erasmus
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid