Although 95% of my work is done in Linux and MySQL, there are those unfortunate times I need to manipulate some data on a Microsoft SQL server. I didn’t want to install any apps on my computer that weren’t open source and I didn’t want to use a Micro$uck app through wine either. So after a few searches I came across an open source project called Sqsh. In this tutorial I’ll show you how to install sqsh and access a Microsoft SQL server from the Linux command line.
Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for ‘isql’, supplied by Sybase. Sqsh is intended to provide much of the functionality provided by a good shell, such as variables, redirection, pipes, back-grounding, job control, history, command completion, and dynamic configuration. Sqsh has quite a few features, this is just a short list, for detailed explanation and examples please visit their site here.
. Provides all commands provided by isql
. Variables are provided in sqsh, much in the same way they are used within a standard shell
. Redirection & Pipes
. As of release 1.2, sqsh supports full csh-style command aliasing
. Command substitution
. Backgrounding & Job Control
. SQL Batch History
. Configurable Exit Status
. Ability to transfer the result set from any command batch to another server (or even the same server) via the Sybase bcp protocol
. Remote Procedure Calls
. Simple Scripting
Now that you know what sqsh does, let’s go ahead and install and start using it. If you are running Ubuntu, it is very easy, just do:
sudo apt-get install freetds sudo apt-get install sqsh
You might also need a couple of other libraries installed depending on your setup, but the 2 apps above should contain everything you need.
If not running Ubuntu, get sqsh’s latest source from sourceforge and follow the install steps in the Install document inside the tar file you will be downloading.
Now that both freetds and sqsh have been installed, go ahead and create an entry at the end of the file /etc/freetds/freetds.conf that includes the M$Sql server you’ll be accessing:
[MSuck] host = msuckserver port = 1433 tds version = 8.0
Now create the sqsh configuration file containing your credentials and any other setting you’d like to have predefined. This file must reside in your home directory, otherwise you’ll have to start sqsh with the -r flag, which specifies the path and file to be used instead:
-r [sqshrc[:sqshrc ...]]
Specifies an alternate .sqshrc file to be processed, rather than the default. If no sqshrc is
supplied following -r, then no initialization files are processed. This flag must be the
first argument supplied on the command line, all other instances will be ignored.
So, let’s do it, create the new file .sqshrc and add your settings (obviously change each of the settings to match your own):
user@laptop:~$ vim ~/.sqshrc \set username=msqlloginname \set password=msqlloginpassword \set database=msqldbname \set style=vert
Now you have both freetds and sqsh installed and configured, just go ahead and run it. For more information, options and in depth usage see the manual, or as one of my bosses used to say RTFM (read the fine manual)
user@laptop:~$ sqsh -SMSuck sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray This is free software with ABSOLUTELY NO WARRANTY For more information type '\warranty' 1> select field1, field2, field3 from table where field1 = 'test'; 2> go field1: test field2: other data field3: (1 row affected) 1> exit user@laptop:~$
That’s it, you are now able to connect to a Microsoft SQL server from the command line in Linux and manipulate your data as you wish. Hopefully this tutorial helped you. Thank you for stopping by and please share with others, after all, code should be free.