Execute a MSSQL query from DOS prompt – Nagios Monitoring using ‘sqlcmd’

MSSQL - sqlcmd

Using ‘sqlcmd’

You can use sqlcmd to execute a MSSQL query right from the DOS prompt. Here’s an example:

C:Userstechpain>sqlcmd -S servername -d dbname -U dbuser-P dbpass -q "select field1 from column1 where field1 like '10%';"
field1
------
100
101
102
105
106
108
109

 (7 rows affected)
1> quit
C:Userstechpain>

sqlcmd sytax

-a packet_size
-A (dedicated administrator connection)
-b (terminate batch job if there is an error)
-c batch_terminator
-C (trust the server certificate)
-d db_name
-e (echo input)
-E (use trusted connection)
-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]
-h rows_per_header
-H workstation_name
-i input_file
-I (enable quoted identifiers)
-k[1 | 2] (remove or replace control characters)
-K application_intent
-l login_timeout
-L[c] (list servers, optional clean output)
-m error_level
-M multisubnet_failover
-N (encrypt connection)
-o output_file
-p[1] (print statistics, optional colon format)
-P password
-q "cmdline query"
-Q "cmdline query" (and exit)
-r[0 | 1] (msgs to stderr)
-R (use client regional settings)
-s col_separator
-S [protocol:]server[instance_name][,port]
-t query_timeout
-u (unicode output file)
-U login_id
-v var = "value"
-V error_severity_level
-w column_width
-W (remove trailing spaces)
-x (disable variable substitution)
-X[1] (disable commands, startup script, environment variables and optional exit)
-y variable_length_type_display_width
-Y fixed_length_type_display_width
-z new_password
-Z new_password (and exit)

-? (usage)

For more details on command line options, see http://technet.microsoft.com/

Nagios MSSQL query

The sqlcmd tool is awesome because it allows you to use Nagios and NSclient/nrpe to query a MSSQL server. Here’s how:

  1. Create a .bat on your MSSQL server that contains your query and sends the output to a .txt file
  2. Configure the Windows firewall and NSclient/nrpe check on the MSSQL server
  3. Configure a Windows NSclient/NRPE check in Nagios to check the .txt file
  4. Profit
Share

Leave a Reply

Your email address will not be published. Required fields are marked *