OSQL: Storing result of a Stored Procedure in a file

By Bill Graziano on 17 October 2000 | Tags: Application Design

Amit writes "What is the exact syntax for storing output of a Stored Procedure or a query in a text file. I was trying bcp utility, but is giving error. Thanks in anticipation"

BCP works ok if you want to store the contents of a table to text file. If you want the contents of a stored procedure it gets complicated. In summary, you must use the stored procedure to fill a table, then use BCP to export the table, and finally you need to clear the table. I wish there was a better way!

Wait. There is and it's called OSQL. OSQL is a command line program that allows you to run SQL Statements. Let's look at the simplest form:

osql /U sa /P password /d pubs /S Server9 /Q "Select * from Authors"

Remember you are typing this into the operating system (command prompt) and NOT Query Analyzer. This will connect to the pubs database on Server9 and run the query Select * from Authors and output the results back to stdout (which is the screen unless you redirect it). And the arguement headers (-U, -P, etc.) are case sensitive. Go figure.

You wanted to get the result of a stored procedure. You can do that like this:

osql /U sa /P password /d pubs /S Server9 /Q "sp_help"

Now we need to save this in a file. The simplest way is like this:

osql /U sa /P password /d pubs /S Server9 /Q "sp_help" -o ofile.txt

This will store the results in a file called ofile.txt. You could store them in a Unicode file using -u ofile.txt. You can also embed the path information in the file name using quotes.

There are a couple of other cool things you can do with this. It's really easy to put the SQL statement you want to run in an environment variable using a batch file. You batch file will look something like this:

set mysqlcommand=sp_help
osql /U sa /P password /d pubs /S Server9 /Q "%mysqlcommand%"

This makes it pretty easy to build a batch system. You can also capture the SQL Server result code at the operating system level using the -b flag.

Other popular settings include disabling headers, setting the row width, using trusted connections and input redirection. With input redirection you can put a series of SQL commands into a file and execute them. Books Online has quite a bit more detail on OSQL if you're so inclined.