DBA Tips Archive for SQL Server
No Title[an error occurred while processing this directive]
by Jeff Hunter, Sr. Database Administrator
Both osql and isql are Connectivity Tools that allow you to connect to SQL Server and run Transact-SQL commands. These are command-line utilities and are the equivalents of the SQL Server Query Analyzer. This short article gives a brief comparison of both command-line utilities along with their usage.
osql.exe is a command-line utility that provides an ODBC-based query interface to SQL Server.
It is a replacement for the
isql.exe command prompt utility based on the DB-Library API.
Both utilities are provided with Microsoft SQL Server 2000. The DB-Library API remains
at a SQL Server 6.5 level; therefore, applications that depend on DB-Library, such as
isql, do not support some SQL Server 2000 features.
isql cannot access columns defined with the ntext data type and truncates
any char, varchar, nchar, or nvarchar columns longer than 255 bytes. It also cannot
retrieve results as XML documents. Except for these limitations in isql, both
isql support the same features.
The osql utility is typically used in these ways:
osql utility is started directly from the operating system
with the case-sensitive options listed here. After
it accepts SQL statements and sends them to SQL Server interactively.
The results are formatted and displayed on the screen (stdout).
Use QUIT or EXIT to exit from osql.
If you do not specify a user name when you start
osql, SQL Server
2000 checks for the environment variables and uses those, for
osqlserver=(server). If no environment
variables are set, the workstation user name is used. If you do
not specify a server, the name of the workstation is used.
If neither the -U or -P options are used, SQL Server 2000 attempts to connect using Windows Authentication Mode. Authentication is based on the Microsoft Windows NT account of the user running osql.
The osql utility uses the ODBC API. The utility uses the SQL Server ODBC driver default settings for the SQL Server 2000 SQL-92 connection options.
isql utility allows you to enter Transact-SQL statements,
system procedures, and script files; and uses DB-Library to
communicate with Microsoft SQL Server 2000.
All DB-Library applications, such as
isql, work as SQL Server 6.5
level clients when connected to SQL Server 2000. They do not support
some SQL Server 2000 features. The
isql utility is based on ODBC and
does support all SQL Server 2000 features. Use
isql to run scripts
isql cannot run. For more information about the restrictions on
SQL Server 6.5 level clients, see Connecting Early Version Clients
to SQL Server 2000 in SQL Server Books Online.
The SQL Query Analyzer default is to save SQL scripts as Unicode files.
isql utility does not support Unicode input files. Attempting to
specify one of these files in the
-i switch results in a 170 error:
Use the osql utility to run these Unicode files. An alternative is to specify ANSI instead of Unicode in the File format list of the SQL Query Analyzer File / Save As dialog box.
Like most DB-Library applications, the
isql utility does not set
any connection options by default. Users must issue SET statements
interactively or in their scripts if they want to use specific
connection option settings.
isql utility is started directly from the operating system
with the case-sensitive options listed here. After starting,
accepts Transact-SQL statements and sends them to SQL Server 2000
interactively. The results are formatted and printed on the standard
output device (the screen). Use QUIT or EXIT to exit from isql.
If you do not specify a user name when you start isql, SQL Server 2000
checks for the environment variables and uses those, for example,
isqlserver=(server). If no environment variables are set,
the workstation user name is used. If you do not specify a
server, the name of the workstation is used.
If neither the
-P options are used, SQL Server 2000 attempts
to connect using Windows Authentication Mode.
Simple login to SQL Server instance:
Login to the instance and to a specific database:
Login to specific instance and database:
Run a script and output it to a file:
Run a specific query:
C:\> osql -U sa -P sapassword -Q "EXIT( select 1 as 'TESTING' )" TESTING ----------- 1 (1 row affected)
List available servers:
The following is a collection of questions that I often see.
ISQL uses dbLib as its mechanism for connecting to the server and acts as a SQL Server 6.5 client when connected (legacy application). So you may not have access to all SQL Server 2000 features. ISQL does not support unicode.
OSQL uses ODBC as its mechanism for connecting to the server and supports SQL Server 2000 fully and is unicode compliant.
These are both command-line utilities and are useful when you have to execute a batch against a database or wish to quickly extract data from a database using a query.
Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.
Last modified on
Saturday, 18-Sep-2010 00:37:47 EDT
Page Count: 11821