Introduction
After using the OSQL.EXE command line utility that comes with SQL Server, enough minor annoyances prompted me to create my own version. Specifically, the features I wanted were:
- The ability to talk to any database, via Odbc or OleDb as well as directly to SQL Server
- Better formatting of output so it's readable on a normal 80 column console
- Ability to save configurable parameters so they don't need re-setting each time you launch the application
SQL.EXE
This is my rough-and-ready solution, which compiles to SQL.EXE, which I wrote in about 30 minutes (it took me somewhat longer to contribute this article!). It has some key differences to OSQL.EXE, viz:
- By default, it operates in "direct" mode, so that typing a query and hitting enter will cause it to execute immediately, rather than waiting for a 'go' command.
- By default, it will truncate the output horizontally to fit across 80 columns, and will limit the number of rows to 50 (but still show the total number). More importantly, it determines the width of each column based on the maximum length of the data, not the field size.
- It takes no command-line parameters, but supports opening database connections from the prompt.
SQL.EXE has several special commands that all begin with a slash.
/open {connection-string}
Establishes a database connection. The connection string should not be placed in quotes, and may be a string suitable for either an Odbc connection, an OleDb connection, or a direct SQL Server connection. This is determined automatically, by looking for key connection string parameters (specifically, "Provider" for OleDb, "Driver" or "DSN" for Odbc, and "Server" for SQL server. It defaults to Odbc if it can't determine a connection type automatically.
/indirect
Switches to 'indirect' mode, which is equivalent to OSQL.EXE: you may enter your query over multiple lines, then run the 'go' command to execute it.
/direct
Switches back to the default 'direct' mode which only supports single-line queries.
/limit [{n}]
Gets or sets the maximum number of rows that will be shown. If n is not specified, it simply shows the current setting.
/width [{n}]
Gets or sets the maximum width of each line displayed. If n is not specified, it simply shows the current setting.
/trunc [{n}]
Gets or sets the maximum number of characters for any given column value, if the total width would exceed the current width setting. If n is not specified, it simply shows the current setting.
/timeout [{n}]
Gets or sets the maximum number of seconds allowed for a query to execute. If n is not specified, it simply shows the current setting. The default is 3600 seconds (an hour), considerably more than the .NET default of 30 seconds.
/save
Saves all current parameters to a file called sql.cmd in your Application Data directory. These parameters are reloaded next time you restart SQL.EXE. This includes the currently open connection, so next time you run the application, you won't have to remember the connection string. It does not include the echo filename.
/restore
Executes all commands in the sql.cmd file written by the /save command. This happens automatically at startup.
/close
Closes the current database connection.
/echo [{filename}|off]
Causes the output of queries to be dumped to a tab-delimited file, suitable for import via SQL Server's BULK INSERT command. Note that the file output is not truncated in any way, regardless of what settings are used for display. You should typically use this command for the results of a single query only, as no column information is written to the file. The file is overwritten each time.
There are three other special commands inherited from osql.exe, namely:
go, which executes any queries entered in indirect mode reset, which clears any queries entered in indirect mode quit, which ends the application
All other commands are passed directly to the database provider as is, and any errors generated are displayed as is.
Change Log
- 15-Jan-07
- Added ability to echo query output to a file
- Fixed problem with only showing first result from batch commands
