SQL For PowerShell, For SQL Newbies

This won’t be an in depth post, just a few tidbits I’ve picked up while diving into writing a few functions involving SQL data and databases.  Note that when I say ‘SQL newbies’, I include myself – if you have any suggestions or tips, they would be quite welcome!

You don’t need to install SQL components

If all you are shooting for is T-SQL queries, grab Invoke-Sqlcmd2 and get to work.  It’s quite simple to use, and there are no dependencies.  Thanks go to Chad and the other contributors!  A few examples:

  • Execute a basic query against one SQL instance:


  • Execute a basic query against three SQL instances, using pipeline input and appending the instance to each result:


  • Compare the resulting data.  The first example uses the default output type (-As DataRow) and gives us errors.  The second example uses the ‘-As PSObject’ parameter to avoid the errors:



  • Checking for values in columns.  I only want a list of servers with a value in VCNumCPU, but the results include rows where this has the DBNull value.  Same fix:  If you want to work with data in PowerShell, stick to the ‘–As PSObject’ output type.  It will waste less time than trying to deal with DBNull yourself : )



  • Parameterize your queries or face everyone’s wrath!


You can install minimal footprint SQL components

Perhaps you want to do more than just run T-SQL queries.  Your DBA might tell you to install the Management Studio, or SQLPSX (details).  If these fit the bill, go for it – if you want a minimal footprint, you can probably get away with only adding the SQL Server Management Object assemblies that you need.

Your DBA might even insist on using PowerShell from SQLPS.  No thank you.

This TechNet article describes how to load the SMO assemblies in any PowerShell session – In the PowerShell ISE, which is much more comfortable than SQLPS, or perhaps in a scheduled task.  Here are the steps you can take:

  • Browse to the SQL Server Feature Pack of your choice
  • Download and install SQLSysClrTypes.msi, SharedManagementObjects.msi, and optionally, PowerShellTools.msi (32 or 64 bit…)
  • Add the SMO assemblies as needed in PowerShell!
#Define assemblies. In this example, I only need the SMO assembly            
    $assemblylist =   "Microsoft.SqlServer.Smo"            

#Loop through assemblies, load them up.            
    foreach ($asm in $assemblylist){            
            $asm = [Reflection.Assembly]::LoadWithPartialName($asm)            
            Write-Warning "$($_ | out-string)"            

#Do stuff using SMO.  Example:            
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" SomeServer            

#More details:            

Extend PowerShell

Now that you know how to run quick T-SQL queries with no dependencies apart from including the Invoke-Sqlcmd2 code, look for opportunities to extend PowerShell!  Write some functions.  Or get ambitious and write a module.

How is this helpful to a non-DBA?

There are a wide variety of products out there that provide no, or minimal PowerShell support, while storing vast troves of useful data in a database.  In some cases, the queries may be readily available with a quick search.

A real world scenario:

  • A co-worker noticed a Microsoft Systems Center Operations Manager (SCOM) alert that he was worried about, asked if we could pull details on how common this alert was over the past few months
  • SCOM stores long term monitoring data in the data warehouse DB.  If I run Get-SCOMAlert, I get alerts from the past two weeks.  Is that helpful?  Certainly not here.
  • Do I want to figure out how to run the pre-made report in the SCOM console, assuming there is one?  Absolutely not.
  • I spent perhaps 30 minutes diving into queries already written by Kevin Holman and a few others, perhaps an hour to write a wrapper.  In the comment based help I included the following example for my co-worker
  • Pull all the alerts from the data warehouse DB.  Could have specified dates, but I wanted everything:


  • Group the alerts by month, to see if there is a regular distribution:


  • 634 in one month, 80 in the next highest month.  We have an anomaly!  Now group by days in the problem month


  • 582 on a single day.  We checked change control and the events calendar, noticed that we have a network outage on that date.  My co-worker’s question was answered – not a regular occurrence.  We now have a tool the team can use to filter, group, and sort SCOM DW alerts in PowerShell to their heart’s content.

Other resources

I’m not too familiar with the SQL side of the house, but I’ve heard the following resources might be helpful:

Have other resources?  Drop them in the comments or find me on Twitter @PSCookieMonster.