DBA Tips Archive for SQL Server

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

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

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. For example, 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 osql and isql support the same features.

The osql utility is typically used in these ways:

The osql utility is started directly from the operating system with the case-sensitive options listed here. After osql starts, 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 example, osqluser=(user) or 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

The 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 that 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. The isql utility does not support Unicode input files. Attempting to specify one of these files in the -i switch results in a 170 error:


Incorrect syntax near ' '.

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.

The isql utility is started directly from the operating system with the case-sensitive options listed here. After starting, isql 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, isqluser=(user) or 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 -U or -P options are used, SQL Server 2000 attempts to connect using Windows Authentication Mode.

Examples - (Using osql)

Connecting to Instance / Database

Running Queries

Special Usage

FAQs

The following is a collection of questions that I often see.

  1. What is difference between ISQL and OSQL?

    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.

About the Author

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-2017 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 jhunter@idevelopment.info.

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: 11525