The osql utility is typically used in these ways:

• Users interactively enter Transact-SQL statements in a manner similar to working on the command prompt. The results are displayed in the command prompt window.
• Users submit an osql job either specifying a single Transact-SQL statement to execute or pointing the utility to a text file that contains Transact-SQL statements to execute. The output is usually directed to a text file, but it also can be displayed in the command prompt window.

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.

Remarks

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 osql utility is based on ODBC and does support all SQL Server 2000 features. Use osql 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 ' '.

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.

1. Simple login to SQL Server instance:

   osql -U sa -P <password>

2. Login to the instance and to a specific database:

   osql -U sa -P <password> -d <database name>

3. Login to specific instance and database:

   osql -U sa -P <password> -d <database name> -S <server name>

Running Queries

1. Run a script and output it to a file:

   osql -U sa -P <password> -i <input file> -o <output file>

2. Run a specific query:

   osql -U sa -P <password> -Q "EXIT( <your sql command> )"

   For example:

   C:\> osql -U sa -P sapassword -Q "select @@version"
    -------------------------------------------------
    Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
      Aug  6 2000 00:57:48
      Copyright (c) 1988-2000 Microsoft Corporation
      Developer Edition on Windows
      NT 5.1 (Build 2600: Service Pack 2)
   
   (1 row affected)

   C:\> osql -U sa -P sapassword -Q "EXIT( select 1 as 'TESTING' )"
    TESTING
    -----------
              1
   
   (1 row affected)

Special Usage

1. List available servers:

   C:\> osql -L
   
   Servers:
       (local)

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.