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


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

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.

Select str(account_no), name from accounts 

More info about this project can be found at

You can download the script from Github at

Please let me know what you think

Happy querying!

Sunday, September 11, 2016

FULL Joins

photo credit: Hold My Hand via photopin (license)
A FULL join are a great way to find data that missing in two joined tables. The full join is the combination of the LEFT and RIGHT join In the case of my example I am comparing inventory between two different tables where an item may be missing in either table.

If you run this SQL you will see that inventory1 is missing items divisible by 7, while inventory2 is missing items divisible by 8.

Sunday, July 17, 2016

Look Ma, I've got an open source project.

I was working on a follow-up post to my Command Line SQL Server Interpreter post  last week; I wanted to add some additional Powershell features I discovered. As I was working on the post, I came I realized I had a dilemma, do I just update the original code or do I create a new copy of the code with the changes?  If I updated the original code, the original post wouldn't make sense, but if I didn't I, someone could end up with the old version of  the code when the would benefit from the new version.  The key term here is 'version', which led me to version control, which led me to GitHub, the place where all the 'cool kids' put their code. So now I have a new and shiny open source project -- RunSQLCommand.  

Its based on the orginal script but I cleaned up the help and some of the comments to make it more usable (and fixed some typos). I've also added a help file. I changed the licence of the new program from MIT to BSD. The primary difference between  the licenses is that the BSD license requires copyright attribution if the code is used, MIT does not. The original script (in the gist)  is still covered by the MIT licence, so if you are using it, or want to use it, you don't need to worry. 

I found GitHub easy to use and they even have a tool to create a separate 'home page' for the project. Unfortunately, I haven't added the features the prompted this yet, so stay tuned.

Saturday, June 11, 2016

Fun with Unicode and SQL Server

I had to debug a situation recently where some exported data didn't work and I stumbled into the world of Unicode characters. Here are some of the fun facts that I learned

A brief (very loose) interpretation of the history of Unicode

In the beginning there were a bunch if people in the basement of some computer lab somewhere who needed to type words on a computer screen. The realized that with a 7 bit value they would have 128 options in which they could put the entire English alphabet. This included  upper and lower case, numbers, punctuation, and whitespace characters. They called it ASCII (American Standard Code for Information Interchange)  and they saw it was good.

One day they realized that they wanted to communicate with sell computers to people who spoke other languages so they added another bit that allowe them to have 256 characters so they could create the accented and special characters of the most of the European languages.They called it Extended ASCII, and they saw it was good.

But then the heard the cry's of their friends in countries that spoke languages like Japanese, Chinese, and Hebrew who wanted to communicate with buy computers from them and the invented Unicode. And here we are.

Notes about Unicode and SQL Sever

  1. There are lots of variants/encoding for Unicode based on my experimentation it appears that SQL server supports UTF-8 encoding with SQL_Latin1_General_CP1_CI_AS collation.
  2. You need to use a Unicode data types (nchar, nvarchar, ntext)
  3. You need to prefix your string with the UPPERCASE letter N  (ex. N'Some Unicode')
  4. All of the lower 256 ASCII characters are compatible with the Unicode (its backwards compatible)
  5. If you try to cast a Unicode char (not part of the 256 ASCII characters) as ASCII you will get a '?' and there is not way to tell if its not actually a question mark.
  6. Unicode data takes up twice the space that ASCII data takes up. ( varchar(8000) = nvarchar(4000) )
  7. Some of applications cannot import Unicode data files
  8. The way your text editor knows that a file has Unicode data is that the first character has special value (that varies depending of the type of encoding).
  9. Microsoft says that Unicode data processes faster.

Use this code snippet to have 'fun' with unicode


Comments, corrections, clarifications are always appreciated in the comments.