Voice of the DBA

Tag: powershell

Saving PowerShell Results to the Clipboard–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Working with results and manipulating them is always something I struggle with a bit in PowerShell. I’ve slowly been learning how to limit results to what I want and then save those out.

Recently, I was reading Learn dbatools in a Month of Lunches, and I learned something new. I can save results to the clipboard easily. Here’s an example:

Let’s say I want to get backups status. I’d run something like this, using the Get-DbaDbBackupHistory cmdlet:

I see results, but I might want to put this into a report or email that I send to others. I can certainly highlight this in the cmdline and copy it, but there’s an easier way.

I can pipe this to clip, and then I don’t see the results.

Then I can CTRL+V and paste this into notepad (or anywhere).

Super handy way to take information from the PoSh cmdline and ensure I get everything. Miss-highlighting when not paying attention has caused me problems before, especially when the results scroll off the screen. Highlighting a lot of information is hard, so using clip is a good trick.

SQLNewBlogger

This was a quick trick I learned while doing something else, so I decided to write a quick post. This took me about 5 minutes to put together.

You could do the same thing, showing how you’d use this in your job.

December 20, 2021
Reading Data from the Command Line in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

In every language I’ve coded in, there is a way to read input from the command line. From INPUT in BASIC, readln in PASCAL, scanf in C, read in LISP, input() in Python, and more, every language can do this.

I knew PowerShell could, and I wanted to find out how to do it. I’m building a CLI tool for SQL Saturday, and I am looking to get user input.

A quick search led me to Read-Host. I probably should have guessed this, but it does what I need. A quick example of where I started. This code gets an event number from a user and then prints if back out, along with a string created from the number:
```
$EventNumber = Read-Host -Prompt "Event Number:"
$DataFilename = "SQLSat$($EventNumber).yml"
# write the data
write-host("Event: $EventNumber")
write-host("File: $DataFilename")
```
I can run this, as shown here, and put in a number. I typed in the first 1022 below:

I’ll use this to get information from the user and then produce a YAML file that will contain what’s needed to publish, or update, an event.

SQLNewBlogger

This post took me about 10 minutes to assemble. I was already working through this process and stopped to jot down an idea, save some code, and make a screenshot. That was about 2 minutes out of my day, and then I went on with coding. Later, I wrote this.

You could do the same thing. Take a minute out of your daily work, sketch a quick post, and then finish it later.
November 22, 2021
Quick CSV Import with dbaTools – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was searching around for some sample data for a project. I stumbled on a CSV of data from the Internet Movie Database (IMDB). I wanted to quickly import this into a database to play with and query. I decided to use dbaTools since my csv python skills were a little rusty and I thought this would be quick.

This post looks at how easy this was.

Disconnected from the Internet

I had downloaded the CSV before getting on a plane, but once I was ready to import this, I had no access to the Internet. This meant I had no access to looking up commands or documentation. As a result, I decided PowerShell and dbatools would be the easiest way to do this.

I ran a quick Get-Help command and got the name of the command. Using wildcards, I could see the dbatools result below.

I then ran “Get-Help Import-DbaCsv” to get the syntax. Using the results below, I wanted to see how quickly this would run.

A Quick Experiment

With the docs above, I created a database and then entered this command.
```
$movie = Import-DbaCsv -SqlInstance localhost -Database IMDB -AutoCreateTable -Path "IMDB-Movie-Data.csv"
```
This was my experiment. Just try some values and see what happens here. This ran in less than a second, and from ADS, I could see the data had been imported.

When I checked my variable, I saw that things had just worked with the 1000 rows imported in less than half a second.

Success!

This wasn’t my main task, but using some tools and some past knowledge, I figured out how to accomplish this task quickly and get back to the process of writing SQL code to query the data.

SQLNewBlogger

My entire purpose here was to write some queries against this data, but I needed to import the data. Either ADS or SSMS have import wizards, but I’ve had various levels of success at times with them. I didn’t want to work through an ETL process. Once I saw how quick dbatools made this, I decided to write this post, based on the ease of getting something done.

You could easily duplicate this post, noting why you needed to do an import and how easy this was. You could compare this to SSMS or ADS, or even write about starting to use dbatools for this purpose.

This took me less than 10 minutes.
November 1, 2021
Unix vs PowerShell

I saw an article on using awk, sed, and grep on Linux. I used to know how to use those, though I was by no means an expert. However, working with a stream of text with an input and output was a valuable skill I’ve used over and over in my career. There are plenty of times when I’ve needed to handle a long set of text, and my practice with Unix in university helped me a lot. I’ve only lightly needed to use Perl and regex in my career, but I was glad I had some idea of what I was doing.

In the last few years, I’ve spent quite a bit of time working with PowerShell (PoSh) instead of text-based utilities. While I found some of the design cumbersome and unintuitive, overall, the idea of working with objects instead of a stream of text is really nice.

PowerShell was devised to get around some of the complexities of these utilities. It was built to be an integrated shell, with lots of improvements on the way one might use the utilities above in a bash or Korn shell. While it can take a bit of getting used to, in many ways, I do think it’s superior to the old paradigm of chaining together lots of utilities.

What’s more, it’s fairly easy to enhance PoSh with your own routines, and many vendors have taken advantage of this to provide APIs and interfaces with their tools. Plenty of corporate Ops and IT departments have done the same thing, publishing routines that anyone in their organization can expect to exist and use.

I loved Unix, way more than DOS in most ways when I was in university. If I’d been a decade younger, I might have even evolved into Linux and pursued a career in that environment. These days, with plenty of MS software, including SQL Server, moving to Linux, I’m happy to change to that platform. However with PoSh being available cross platform, I think I’d be looking to use PowerShell as my shell and scripting platform of choice, even on Linux.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

September 13, 2021