Export (0) Print
Expand All
8 out of 13 rated this helpful - Rate this topic

Running Transact-SQL Script Files Using osql

SQL Server 2000

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)

See Also

osql Utility

Running the osql Utility

Running Transact-SQL Statements Interactively Using osql

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.