Jason K. Firth, C.E.T.

Instrumentation, Control, and Automation

sqlcmd- A means to doing SQL commands from the command line

Jan 032015

January 3, 2015

I'm Jason Firth.

 

Last time, I posted about openness. One of the ways openness can help everyone is by providing flexibility to do things that may not have otherwise been possible.

At this point, many software packages use Microsoft SQL server as a front-end. Pi Historian and Wonderware Historian, for example, both use SQL Server as a front-end.

This provides some really neat opportunities. You can automate the retrieval or analysis of data from the historian, for example. Visual Studio Express is available for free, and includes all the APIs for communicating with an SQL server.

Let's say you don't want to do anything that complicated. What if you just want to run a simple query and spit out a simple table?

If you're running a computer with SQL Server or the free to download and use SQL Server express, you can use the sqlcmd command from the command line.

You can use the command line "sqlcmd -S [protocol:]server[\instance_name][,port] -U [userid] -p [password]" to connect to a command line instance, but the really interesting part is that you can use "-i input_file[,input_file2...]" or "-o output_file" to automate the running of certain queries.

The input file is a script written in TRANSACT-SQL, (that "Select * where" stuff).

Knowing this, you can pull data and manipulate data from the command line, or from batch files. That isn't something you may want to use for everything on a regular basis, but it's a great little tool to have in your back pocket for those times when you have to get a little script going quickly.

 

Thanks for reading!

There are no published comments.

New comment