Click here to Skip to main content
Click here to Skip to main content

Generating osql Batch Scripts

, 8 Jan 2006 CPOL
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.


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.


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",
oscript.Server = "MyServer";
oscript.Database = "Northwind";
oscript.UseIntegratedSecurity = true;
oscript.UseReportFile = true;
oscript.ReportFile = "Report.txt";

//generate the file

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.


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!


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


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


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

GeneralRe: Making sure scripts get installed in correct order PinmemberMichael Erasmus10-Nov-05 19:29 
GeneralRe: Making sure scripts get installed in correct order PinmemberKant11-Nov-05 10:58 
GeneralRe: Making sure scripts get installed in correct order PinmemberVertyg011-Nov-05 22:41 
GeneralRe: Making sure scripts get installed in correct order Pinmemberscafrithuric25-Feb-10 9: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 | Terms of Use | Mobile
Web04 | 2.8.150327.1 | Last Updated 8 Jan 2006
Article Copyright 2005 by Michael Erasmus
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid