Export (0) Print
Expand All

Running the osql Utility

SQL Server 2000

Running the osql Utility

  Topic last updated -- January 2004

To run the osql utility, first open a command prompt window by clicking Start, clicking All Programs, pointing to Accessories, and then clicking Command Prompt. In the Command Prompt window, type the command you want to run at the blinking underscore, which is called the command prompt.

You run the osql utility by typing osql at the command prompt, followed by a list of switches that specify the options you require. For a complete list of the switches supported by osql, see osql Utility. The switches used most often are:

  • A server switch (-S) that identifies the instance of SQL Server to which osql connects.
    • If you specify -S ComputerName, osql connects to the default instance on the specified computer.

    • If you specify -S ComputerName\InstanceName, osql connects to the specified named instance on that computer.

    • If you do not specify -S, osql connects to the default instance on the same computer on which osql is running.

      For more information about instances, see Multiple Instances of SQL Server.

  • Authentication switches (-E, -U, -P) that specify the credentials osql uses to connect to the instance of SQL Server.
    • If you specify -E, osql requests a Windows Authentication connection using your current Windows login account. This is the most secure option for connecting to an instance of SQL Server.

    • If the instance of SQL Server only supports SQL Server Authentication, however, you must use the -U switch to specify a SQL Server login account and the -P switch to specify the password for that account.

      Security Note  When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.

      Security Note  To mask your password, do not specify the -P switch along with the -U switch. Instead, after specifying osql along with the -U switch and other switches (do not specify -P), press enter, and osql will prompt you for a password. This method will ensure that your password is masked when it is entered.

      For more information about logging in to an instance of SQL Server, see Logins.

  • Input switches (-Q, -q, and -i) that identify the location of the input to osql.
    • If you specify -q Transact-SQL statement, osql will connect to an instance of SQL Server, run the specified Transact-SQL statement, and then prompt you to enter additional Transact-SQL statements or osql commands.

    • If you specify -Q Transact-SQL statement, osql will connect to an instance of SQL Server, run the Transact-SQL statement, disconnect, and terminate.

    • If you have a text file that contains one or more Transact-SQL statements, use the -i filename switch to tell osql to use that file as its source of Transact-SQL statements. osql connects to an instance of SQL Server, sends all the Transact-SQL statements in the input file, disconnects, and terminates.

    • If you do not specify -Q, -q, or -i, osql connects to an instance of SQL Server and then displays a 1> followed by a blinking underscore called the osql prompt. You can then enter Transact-SQL statements or osql commands at the osql prompt. For more information about osql commands, see osql Utility.
  • An output switch (-o) that specifies the file in which osql is to place its output. If you specify -o filename, osql creates a text file of that name and places all of its output in the specified file. If you do not specify -o, osql displays all of its output in the same command prompt window in which osql is running.
Examples of Running the osql Utility

The following are common uses of the osql utility:

  • Connecting to a default instance using Windows Authentication to interactively run Transact-SQL statements:
    osql -E -S ComputerName
  • Connecting to a named instance using Windows Authentication to interactively run Transact-SQL statements:
    osql -E -S ComputerName\InstanceName
  • Connecting to a named instance using Windows Authentication and specifying input and output files:
    osql -E -S ComputerName\InstanceName -i MyScript.sql -o MyOutput.rpt
  • Connecting to the default instance on the local computer using Windows Authentication, executing a query, and having osql remain running after the query completes:
    osql -E -q "SELECT * FROM Northwind.dbo.Shippers"
  • Connecting to the default instance on the local computer using Windows Authentication, executing a query, directing the output to a file, and having osql terminate after the query completes:
    osql -E -Q "SELECT * FROM Northwind.dbo.Shippers" -o MyOutput.rpt
  • Connecting to a named instance using SQL Server Authentication to interactively run Transact-SQL statements, with osql prompting you for your password:
    osql -U MyLogin -S ComputerName\InstanceName
  • Getting a list of the switches supported by the osql utility:
    osql -?

See Also

Running Transact-SQL Statements Interactively Using osql

Running Transact-SQL Script Files Using osql

Administering SQL Server Using osql

Show:
© 2014 Microsoft