There are different methods to run queries against an SQL Server from a Linux machine. The one I am using is mssql-cli (https://github.com/dbcli/mssql-cli)

As you can check in the related github website, it has the following features:

  • Auto-completion
  • Syntax highlighting
  • Query history
  • Configuration file support
  • Multi-line queries

Installation

The -Q parameter

It has been packetized for a lot of distributions (see Package Installation Instructions), but at least in case of the Debian 9, the packetized version does not include the -Q parameter that allows setting the query to run.

Depending on your needs it might very important because this could allow automate tasks to collect data. For example, for an Icinga check. Otherwise, it would need interaction to run the query.

Install via pip

This is the method i have used to install it in a Debian 9:

– In case you don’t have the PIP tool (python package installer)

apt install python-pip

– And to install the SQL server command-line utility itself:

pip install mssql-cli

Example usage

Interactive

The parameters are:
-S    SQL Server
-d    database
-U    SQL Server user
-P    SQL Server password

$ mssql-cli -S "SQLSERVER01" -d "master" -U dbauser -P "dbapassword"
master>

Now you have the prompt with the database name, you can start running queries.


master> select top 1 * from sysprocesses;
Time: 0.507s
-[ RECORD 1 ]-------------------------
spid         | 2000
kpid         | 0
blocked      | 0
waittype     | 0x0000
waittime     | 0
lastwaittype | MISCELLANEOUS
waitresource |
dbid         | 5
...

Non-Interactive

By using the -Q parameter I have mentioned before

$ mssql-cli -S "SQLSERVER01" -d "master" -U dbauser -P "dbapassword" -Q "select top 4 spid,login_time,status from sysprocesses;"
+--------+-------------------------+--------------------------------+
| spid   | login_time              | status                         |
|--------+-------------------------+--------------------------------|
| 2000   | 2020-01-15 12:59:19.987 | sleeping                       |
| 3000   | 2020-01-15 12:59:14.893 | sleeping                       |
| 5000   | 2020-01-15 11:44:05.233 | sleeping                       |
| 1000   | 2020-01-15 11:56:11.197 | sleeping                       |
+--------+-------------------------+--------------------------------+
(4 rows affected)

$

In a future post, I’ll show how to use Icinga to monitor the values obtained from a SQL server query so that we can monitor them.