Running Transact-SQL Script Files Using osql

Accessing and Changing Relational Data
Running Transact-SQL Script Files Using osql

Topic last updated -- January 2004

You can use osql to execute database script files, which are text files containing a mix of Transact-SQL statements and osql commands. osql works with the statements and commands in the script file in a manner very similar to how it works with statements and commands entered interactively. The main difference is that osql reads through the input file without pause instead of waiting for a user to enter the statements and commands.

There are different ways to create database script files:

* You can interactively build and debug a set of Transact-SQL statements in SQL Server Query Analyzer, and then save the contents of the Query window as a script file.

* You can create a text file containing Transact-SQL statements using a text editor such as notepad.

* You can use SQL Server Enterprise Manager to generate scripts for creating objects in a database. For more information, see Documenting and Scripting Databases.

For more information about the uses of Transact-SQL scripts, see Transact-SQL Scripts.
Example of Running a Script Using osql

This is an example of creating a script file and running the script using osql.

Open Notepad and type the following Transact-SQL statements and osql commands:

USE Northwind

GO

SELECT SupplierID, CompanyName

FROM Suppliers

WHERE CompanyName LIKE 'T%'

GO

Use the File/Save As menu to save this as a file named MyScript.sql in a folder C:\MyFolder. Run the following command from the command prompt to run the script and place the output in a file named MyOutput.rpt in the same folder:

osql -E -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.rpt

When you view the contents of MyOutput.rpt in Notepad, you will see:

1> 2> 1> 2> 3> 4> SupplierID CompanyName

----------- ----------------------------------------

4 Tokyo Traders

(1 row affected)

1>

You can use the -n switch to suppress the batch line numbers (1> 2> 1> 2> 3> 4>):

osql -E -h-1 -i C:\MyFolder\MyScript.sql -o C:\MyFolder\NoHeaders.rpt

When you view the contents of NoHeaders.rpt, you will see:

SupplierID CompanyName

----------- ----------------------------------------

4 Tokyo Traders

(1 row affected)