Sometimes it is a great idea to run a SQL stored procedure from a command line and put the output into a text file. Here’s how you can do that. First, define your stored procedure to output what you want to see in the log.

Here’s an example that makes the filename like LOGmmddyyyy.txt:

oSQL -E -Q "use AdventureWorks;select count(*) NumberOfEmployees from humanresources.employee" -o
c:\maint\Log%date:~4,2%%date:~7,2%%date:~10%.txt

The output you will find in file C:\maint\Log06172009.txt is:

NumberOfEmployees
———————
290

(1 Row Affected)

Options that you can use with OSQL:

-E Trusted connection – don’t need to specify username/password. Authenticates with user running.
-U and -P username and password
-q run query, does not exit.
-Q Runs query and exits.
-o Destination File Output.
-h Remove one line from the output.
-p print statistics. Ex:
Network packet size (bytes): 4096 1 xact:
Clock Time (ms.): total 0 avg 0 (1.00 xacts per sec.)
-L List the SQL servers on the network (osql -L)

Example without header (column names):
oSQL -E -h-1 -Q "use AdventureWorks;select count(*) NumberOfEmployees from humanresources.employee" -o
c:\maint\Log%date:~4,2%%date:~7,2%%date:~10%.txt

Another option would be to put the query in its own file. Create a file called employees.sql and enter the following:

use AdventureWorks
GO
select count(*) NumberOfEmployees from humanresources.employee
GO

Now run the command again, but replace -Q “query” with -i filename:
oSQL -E -h-1 -i employees.sql -o c:\maint\Log%date:~4,2%%date:~7,2%%date:~10%.txt

As you can see, oSQL is very useful for running procedures from command line. You can enter a query with the -q or -Q argument, specify a file that has SQL commands with the -i argument, and also save the output of those procedures into the filename specified in the -o argument.

Tagged: , , , , ,