Saturday, February 18, 2017

The Pigs Have Crashed -- Running runSQLcmd.ps1 on Linux

I decided that I would 'port' my RunSQLCommand project to Powershell on Linux this weekend. Here's what what I did, what worked, and what failed.

I loaded Powershell on my Ubuntu Linux 16.04 VM according to Microsoft's instructions on github. That went very smoothly. I didn't even get the errors they warned about. (I think it's because I already have SQL Server installed on the VM)

Once the installation was complete I typed 'powershell'  at the command line  and ...boom.. it worked.




















So far so good.

Next I uploaded my script to the Linux box and 'Linux-ized' it.

I added the following command to the first line of the file with nano. (Nano is an easy to use linux editor. To open a file with it, type nano filename )

#! /usr/bin/powershell

This is how linux knows what interpreter to run your script with. (Rather than using the file extension that Windows uses.)

I also changed the authentication to sql server authentication from trusted, and added a valid username and password in the "Configuration" section of the script

$trusted="NO"
$user="linuxuser"
$password="password"

Then I Saved the file. (CTRL-X Y <ENTER>)































I also changed the file permissions so that it was executable.

chmod 744 runSQLcmd.ps1


Then came the moment of truth....

























The good news,  the script ran, it connected to database, and the help command worked.
The bad news,  the the DataTable object that I use to output data isn't part of the Linux powershell port.  I'm going to have figure out another option. 


Overall Powershell for Linux works the way I would expect a Linux shell to work. But, I am a bit disappointed that its missing some core libraries.

Sunday, February 12, 2017

Graphical Linux ls Command Output Quick Reference for SQL Server Admins

Linux usage has become more prevalent in the Microsoft ecosystem, especially with the release of SQL Server for Linux.

 This is graphical quick reference to the long listing output of the ls command (ls -l). Hopefully it will be of help a new SQL Server DBA on Linux.


Related info: ls command quick reference

Graphical Linux ls Command Output Quick Ref


Sunday, January 1, 2017

Update to runSQLCommand Project

Octocat
I've added a new feature to the runSQLCommand project-- You now have the option to output to a to the Powershell gridview (out-gridview) control. This control lets you filter, sort, and re-organize the data.  When you start up the script it still defaults to output to the console, but you can switch it to pop up the grid control by typing 'ouput grid'.

The grid control does have some limitations based on the version of Powershell you are running, in Powershell Version 3 it was about 600 rows and 30 columns. I suggest that you test it out on your system to see what it gives you.  Another quirk is that it puts thousand separators in numbers. To avoid this you can format the output with the str command in sql.

Example:
Select str(account_no), name from accounts 


More info about this project can be found at https://jeffspillerconsulting.github.io/RunSQLCommand/

You can download the script from Github at
  https://github.com/JeffSpillerConsulting/RunSQLCommand/blob/master/runSQLcmd.ps1

Please let me know what you think

Happy querying!