HOWTO: Convert SQL data to PowerShell Objects

As you develop your PowerShell skills, you’ll start to see how they can be useful everywhere – even in places where it might not seem obvious.
Let’s say you have a SQL database that you need to extract information from but you’re not terribly strong with SQL.  You are pretty good with PowerShell though.
Wouldn’t it be great if you could easily "convert" your SQL data into a native PowerShell object and then do all of your filtering and customization there?  In a language you know and love?

I found myself in that exact situation and I’m pleased to say I found a simple and elegant solution.

Sidenote: As I was preparing for this blog post, I realized it can be incredibly difficult to build screenshots and database queries that don’t contain potentially sensitive information.

Let’s make up a scenario in which we have a database called ‘rdm’ and inside that there is a table called ‘ConnectionLog’.
We want to find the 5 dates in which the user ‘rvance’ logged in most frequently.  Let’s assume we are doing some kind of security analysis. 

How would you approach this?  In my case I would probably start by launching SQL Management Studio and taking a stab at drafting a query. 
I actually did this for real for the purposes of this demo and came up with the following:

It looks relatively straight forward, but if you have little to no experience with SQL, it’s going to look pretty complex, especially the varchar and "102" references.

How can we accomplish exactly the same thing using PowerShell?  Well you first have to make sure that you have the sqlps module which comes with SQL Server Management Studio.
You can confirm this is available by opening a PowerShell prompt and typing Import-Module sqlps.  We will assume this is present.

So what do we do?  Check this out:

If you are not comfortable with PowerShell, this may look even more complicated.  But if you are comfortable with PowerShell and specifically hash tables, this will make complete sense.
It also doesn’t contain any arbitrary values such as "102" in order to function.  Let’s break this down:

First we want to create a here string that contains the query we want to execute.  This can be as simple or as complex as you’d like. 
Since we are assuming however you have limited SQL experience, we’ll for now say we’re going to pull all of the data into PowerShell and then filter it from there.
This follows the syntax of SELECT * FROM tablename.  Depending on the amount of data involved, this may require some additional tweaking.

Next we use the cmdlet Invoke-SQLCMD which accepts as parameters the query to run, the SQL instance and the database name.  Note if you have multiple instances, it would be computername\instancename.
We save the output from that command into a variable.  After that we are now free to leverage our PowerShell expertise to do whatever we’d like!  Write it to a file, send an email, use as variables in a script, anything!

It ultimately is all achieved with just one line of code:

$SQLData = Invoke-SQLCmd –Query $Query –ServerInstance computername\instancename –database databasename

Note: Invoke-SQLCmd has a "username" parameter but don’t be fooled.  This only works for local SQL accounts.  You cannot use this to log in with a Windows credential.  To do that you need to run your PowerShell session as the user you want to connect with.

That’s all there is to converting SQL data into a native PowerShell object!