OSQL – The details

Using osql

I covered some basic uses of the osql command line SQL utility in my previous blog.  Now, to dig into some of the details.

The basic osql options that I normally use are:

    -S
    -d
    -i
    -o
    -U
    -P     -w

So, let’s look at an example, using the following values:
    SERVER=MySQLServer
    DB-NAME=MyDB
    LOGIN-ID=MyLogin
    PASSWORD=MyPassword
    INPUT FILE=MySQLScript.sql
    OUTPUT FILE=MySQL.out
    WIDTH=100

Using these, the command line would be:
    osql -S MySQLServer -d MyDB -U MyLogin -P MyPassword -i MySQLScript -o MySQL.out -w 100

The result of this, is that osql will connect to the database MyDB on the SQL server MySQLServer.  It will use the user name and password supplied (MyLogin, and MyPassword).  Then execute the script MySQLScript, sending the output to the file MySQL.out.  The output will be wrapped at 100 characters, instead of the standard 80 characters.

The osql utility can run a single query command, instead of a script if desired.  This is done using the "Q" options.  So, using -Q "SELECT COUNT(*) FROM CUSTOMERS", will run a count on your customer table.  The quotes are required around the actual SQL command.

There are several other options, for dealing with time out values, using trusted connections instead of passwords, headers, column separators, and such.  I rarely use these.

As mentioned in my last blog, an osql command such as this can be put into a batch file, and scheduled to run automatically.  Another option would be to put it in a batch file, and create a shortcut to the batch file on the Windows desktop.  Thus, it offers a simple method of letting non-technical users execute SQL scripts directly from their desktop.

For osql assistance contact the CCS Retail Support Department.

Dave.

 

Leave a Reply