OSQL

What is the purpose of osql.exe? I thought it was just a tool that an admin would use to access an Oracle instance data. I am trying to figure out if osql.exe is needed on a system where no user interaction is performed on the database(no development). Is it needed when a user fills out a form and the data from that form is sent to the Oracle db using the osql.exe functions?

The ISQL and OSQL command-line utilities can be used to execute SQL statements, stored procedures, and script files from the command prompt. Commands that can be entered interactively via Query Analyzer can be placed in batch files and executed via ISQL/OSQL. In addition, the output from these files can be routed to a file that allows daily reports and tasks of this nature to be run unattended.

The ISQL utility uses DB-LIB to communicate with SQL Server, whereas OSQL uses ODBC. Because ISQL is based on the DB-LIB interface, it does not support Unicode data. DB-Library has not been enhanced to support any of the new features in SQL Server 7.0 or 2000 and is only maintained for backward compatibility. Microsoft recommends that you use OSQL instead of ISQL for any command-line procedures.

The command-line parameters for ISQL and OSQL are similar with only a few differences. The command-line parameters for both utilities allow you to specify the query or file you want to execute, the target server and database, as well as the location for the resulting output.

TIP

According to the Books Online documentation, the ISQL utility (which is based on Db-Library) does not support named instances. Contrary to the documentation, I have been able to connect with ISQL using named instance, but you might encounter problems. If you do experience problems, one workaround is to set up an alias for the named instance using the Client Network utility. After this alias has been created, then the alias name can be used with the \S parameter in ISQL. Another alternative is to use OSQL, which does support named instances and all other SQL Server 2000 features.

A simple OSQL example follows:

OSQL /sserver_name /E /iinput_file  /ooutput_file

This example shows the basic syntax needed to run a query from a SQL script file using the default server instance. A trusted connection and an output file for the results are specified as well. The same syntax can be used for the ISQL utility.

In addition to using the utilities to run SQL queries, you can also use them by specifying the -L option. This option will list the names of the SQL servers currently on the network. You might want to use this option when you are troubleshooting a client’s connection and you want to know which SQL servers the particular workstation can see.

The ISQL.EXE and OSQL.EXE files are located, by default, with the SQL Server tools in Microsoft SQL Server\80\Tools\Binn\.

If you have more information or know of another definition for OSQL, please let us know so that we can review it and add that information to our database.

Every attempt has been made to provide you with the correct acronym for OSQL. If we missed the mark, we would greatly appreciate your help by entering the correct or alternate meaning in the box below.

Definitions have been compiled from popular search engines and multiple results provided for your review.

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.

At one point in time I was tasked with running over 70 databases on more than a dozen servers without any of the costly third-party tools that exist to accomplish such tasks, and I often get asked how can you handle such large numbers of databases. This is a short article that will show you how to use the handy OSQL command line utility, provided with SQL Server, to execute repetitive tasks against large numbers of databases or to manage large numbers of servers.

The OSQL command line utility allows you to execute Transact-SQL statements, system procedures, and script files against multiple servers and databases by using ODBC connections to those servers and databases. The syntax of the OSQL utility allows you to specify which server, database, login, password, input file, output file, as well as formatting functions you want to run with your SQL script. By creating a small BAT file containing this information for the servers and database you manage and creating a SQL script file, you can easily perform repetitive SQL tasks with one or two mouse clicks.

-? used to display the syntax for OSQL switches.
-L will list the locally configures servers and the names of the servers broadcasting on your network.
-U login_id is the user login to use for the connection to the server and database and is case sensitive.
-P password is the password for the login_id specified. If -P is not used then OSQL will prompt for a password, and if -P is not specified then OSQL will use a NULL value as the password. -P is case sensitive. Another option is to set a password for OSQL by using the OSQLPASSWORD environment variable at the command line.

C:\>SET OSQLPASSWORD=password.

This will allow you to not specify the password and cause OSQL to check for the environment variable before it uses the NULL value which allows you to keep from having to hard code the password into the batch file.
-E will cause OSQL to use a trusted connection.
-S server_name[\instance_name] will specify the server and in the case of SQL Server 2000 instances the SQL Server instance you want to connect to. Connecting to just the server name will cause OSQL to try to connect to the default instance of SQL Server.
-H wksta_name is a workstation name that will be stored in the sysprocesses system table with a default of the current computer name.
-d db_name specifies which database to use for the SQL statement.
-l time_out specifies the number of seconds to wait before OSQL login times out with a default of 8 seconds.
-t time_out specifies the number of seconds before a command times out with a default of never.
-h headers specifies the number of rows to print between column headings with a default of all rows after the column headings. A -1 will specify that no headers be printed: note that if -1 is used do not include a space between -h and -1 (-h-1).
-s col_separator will specify the column-separator character with a default of a blank space. To use characters that have special meaning to the operating system (| ; & < >), enclose the character in double quotation marks (”).
-w column_width will allow you to set the screen width for output with a default of 80 characters. When an output line has reached its maximum screen width, it is broken into multiple lines.
-a packet_size will allow you to request a different-sized packet with a default size of the server default. Valid values are 512 through 65535. Increased packet size can enhance performance on larger script execution where the amount of SQL statements between GO commands is substantial.
-e will echo the input into the output file.
-I will set the QUOTED_IDENTIFIER connection option on.
-D data_source_name will connect to an ODBC data source that is defined using the ODBC driver for Microsoft SQL Server and will use the options specified in the data source. This option does not work with data sources defined for other drivers.
-c cmd_end will specify the command terminator.
-q “query” will execute a query when OSQL is started but will not exit OSQL when the query completes. You can use %variables, or environment %variables% in a batch file by setting the variable beforehand.

SET table = sysobjects
OSQL /q “Select * from %table%”

Make sure you use double quotation marks around the query and single quotation marks around anything embedded in the query.
-Q “query” will execute a query and exit OSQL. Like -q use double quotation marks around the query and single quotation marks around anything embedded in the query.
-n will remove the numbering and the prompt symbol (>) from input lines. I use this parameter to keep anything from being written to an output file if no errors occurs, so all I have to do is look for the output files to be 0 in size to know the statement completed correctly.
-m error_level will customize the display of error messages. The message number, state, and error level are displayed for errors of the specified severity level or higher. Nothing is displayed for errors of levels lower than the specified level. Use -1 to specify that all headers are returned with messages, even informational messages. If using -1, there must be no space between the parameter and the setting (-m-1, not -m -1).
-r {0 | 1} will redirect the message output to the screen. If a parameter is not specified or if 0 is specified then only error messages with a severity level 11 or higher are redirected. If you specify 1, all message output (including “print”) is redirected.
-i input_file will identify the file that contains a batch of SQL statements or stored procedures. The less than (<) comparison operator can be used in place of -i.
-o output_file will identify the file that receives output from OSQL. The greater than (>) comparison operator can be used in place of -o.
-p will print performance statistics.
-b will specifies that OSQL exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0.
-u will specify that output_file is stored in Unicode format, regardless of the format of the input_file.
-R will specify that the SQL Server ODBC driver use client settings when converting currency, date, and time data to character data.
-O will specify that certain OSQL features be deactivated to match the behavior of earlier versions of ISQL.

These features are:
–EOF batch processing
–Automatic console width scaling
–Wide messages

-O will also set the default DOS ERRORLEVEL value to -1.

Putting It All Together

Using OSQL can be as made as fancy as you want it to be, but I find that the creation of two simple files will allow me to accomplish most the tasks I’ve needed to in a quick manner against numerous databases.

The first thing you need to do is to create your batch file or files based on the database groupings you want to run statement against. Using a simple text editor, create the file with your parameters and save the file with a .bat extension. You can create this batch file to use a text file as the input file which will keep you from having to adjust parameters each time you want to run the batch process. A sample of what I do can be found in the following example:

OSQL -Usa -Ppassword -Sserver1 -dDatabase1 -n
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db1.txt

OSQL -Usa -Ppassword -Sserver2 -dDatabase2 -n
-iC:\OSQL_SCRIPTS\SQL_SCRIPTS\sqlscript.sql 
-oC:\OSQL_SCRIPTS\OUTPUT\OSQLoutput_db2.txt

What I usually do is create and test the SQL script in Query Analyzer and then save that script to a file named sqlscript.sql (I actually have several files with different names for different groupings of databases).

When you execute the .bat file, it will start up OSQL, connect to each defined server, execute a USE statement for the database defined, and execute the SQL command contained in the SQL script file creating a separate output file for each connection. Learning to format the output to a form that is readable takes some practice, but I usually use OSQL to find the location of the data I am interested in and then run the query in Query Analyzer so I can format the data in a quick and easy manner.

Summary

Taking the time to create OSQL bat files for groups of servers and/or databases and using these files to manage your environment for repetitive tasks will pay off as you find yourself only needing a few minutes to create a database object or run a query against dozens of databases located on dozens of servers. So keep asking for the expensive toys some think they need to mange multiple databases, but until your company agrees to your request, save yourself a lot of time and frustration by figuring out how you can use OSQL to manage your environment.

If you have repetitive tasks to run, multiple SQL servers to administer, or a very large sequence of commands to execute, then the OSQL utility may be the tool to use. OSQL is capable of running both scripts and interactive commands. It is started from the command line and can be executed manually or by a scheduled task. With over twenty-five switch commands, OSQL can usually be configured to execute, as your application requires. OSQL does not have a user interface. So many times, scripts are created in Query Analyzer, saved and then run by OSQL.

OSQL vs. ISQL and Query Analyzer

There is a great deal of overlap between ISQL and OSQL. Both support input scripts, output scripts, and most of the same switch arguments. OSQL has no interface. It will only accept a typed command line, or a saved script. In spite of this disadvantage, sometimes ISQL and Query Analyzer cannot accomplish the required task. Working with MSDE is one example. Query Analyzer is not included with the Microsoft Desktop Engine. When developing an application on MSDE, or needing to do MSDE administration, the OSQL utility is the only tool included. Another key difference between ISQL and OSQL is the base library each tool was built on. ISQL is developed on the DB Library, as opposed to OSQL being developed on ODBC. The DB Library works at the SQL 6.5 standard. This difference means ISQL, or any application developed on the DB Library, dose not support some of the new SQL 2000 features. The entire list of unsupported features can found in Books on Line under the title “Connecting Early Version Clients to SQL Server 2000.” Some of the main limitations of ISQL include char and varchars defined greater than 255 bytes will be non accessible, big ints will be converted to decimals, sql_variants will be converted to nvarchars, XML results may not be retrieved, and bit fields that are null will be reported as not null with a value of 0. OSQL and Query Analyzer will support all of the SQL 2000 features.

OSQL ad hoc query examples

In these beginning tests, we will execute ad hoc queries from the command line. This first command assumes SQL server is local on your machine and you have Windows trusted connection, rather than a SQL uid and password. Open the command prompt and enter:

OSQL -E -Q “SELECT * FROM sysloings”

The contents of the syslogins table should scroll down the command window. In the above statement, -E tells OSQL to used a trusted connection rather than a SQL uid. The -Q is the query statement. Because no database was specified, master was used. To specify a database, change the statement to:

OSQL -E -d pubs -Q “SELECT * FROM authors”

The switches are case sensitive. Moreover, many times a lower case letter has no relation to an upper case letter. Lower case p is used for print performance statistics while an upper case P is used to specify a SQL password.

To use SQL security rather than Windows security, remove the -E and change the statement to:

OSQL -U sa -P secret  -d pubs -Q “SELECT * FROM authors”

OSQL scripts

In these next examples, we will create and save TSQL scripts, and then run them from OSQL. Query Analyzer is a standard choice for script creation because of the color coding. Open Query Analyzer and enter:

USE pubs
GO
SELECT * FROM authors
GO

Save this script to your hard drive, and then from the command line, enter an OSQL statement using the -i switch to specify an input file. The authors table should be returned.

OSQL -E -i c:\temp\q1.sql

The results of the query can be captured to an output file, rather than appearing on the screen. Change the command line to include the -o parameter, for output.

OSQL -E -i c:\temp\q1.sql -o c:\temp\resutls.txt

OSQL should create a text output file. The -u switch can be used to control the output file being either Unicode or OEM.

System Commands

Operating system commands can also be executed from inside the TSQL script. The key !! is used to specify this. Change the Query Analyzer script and save it as:

!! dir c:\temp
GO
USE pubs
GO
SELECT * FROM authors
GO

Now our output file will include the directory listing of the temp folder in addition to the authors’ results. Also, note this script will not run in Query Analyzer. The !! directive is not supported. Query Analyzer color coding is helpful in code layout, but the script testing will need to be done from OSQL.

Error Handling

OSQL supports the RAISERROR command for returning custom error messages. To use raise error, the database name, id, error severity and state should be included. Using RAISERROR will cause the script to terminate. Modify the Query Analyzer script to:

!! dir c:\temp
GO
DECLARE @DBID int
SET @DBID = DB_ID()
DELCARE @DBNAME nvarchar(128)
SET @DBNAME = DB_NAME()
RAISERROR(’my error’, 18, 127, @DBID, @DBNAME)
USE pubs
GO
SELECT * FROM authors
GO

Running the script will now output our directory listing, from the “!! dir c:\temp” command, followed by the raise error. The remaining script, changing to pubs and selecting from authors will not occur. RAISERROR will terminate the script.

Leaving a script can also be done by calling QUIT or EXIT from inside OSQL. Neither of these will return an error code, but EXIT can execute a statement prior to quitting. For example: EXIT(SELECT @@ROWCOUNT).
Conclusion

When administering the Microsoft Desktop Engine, OSQL is a free way to run statements. For standard SQL Server environments, OSQL can be used to help automate long or repetitive tasks by reusing scripts. OSQL is also a good choice to run database setup scripts during application install procedures.