PowerShell Interfaces with Other Hammers

This month’s installment of T-SQL Tuesday is hosted by Pat Wright (blog | twitter). Pat says: “So the topic I have chosen for this month is Automation! It can be Automation with T-SQL or with PowerShell or a mix of both. Give us your best tips/tricks and ideas for making our lives easier through Automation.”

I have to tell you, this doesn’t get old:

imageI love that somebody on the other side of the planet is telling someone else that I ‘probably have a PowerShell script to solve their problem’. The funny thing is that a lot of the time I don’t have a script for the problem they’re trying to solve *yet*. I love it when someone gets referred to me and I don’t have a script because it gives me a chance to go explore the language and figure out something I don’t know. PowerShell makes this quest pretty fast.

imageIronically Paul White ( blog | twitter ) didn’t know I had a script for that, he was just making a joke. Well, actually, I didn’t have a script for that. I did however know right where to find the person that did; and that is even better than if I had a script of my own. Why is it better that I had to point someone some where else? Could I not have figured this script out own my own? No that’s not it at all.

imagePowerShell is NOT a hammer it’s an Automation Language. Better yet PowerShell is a Toolset, and it happens to come with some pretty freaking awesome hammers built in. We all understand that SQL Server isn’t just a hammer, that it’s an entire toolset. Inside of that toolset is: T-SQL, SQL Agent, Profiler, SSIS, the SMO, SSRS, SSAS etc… just to name a few.

The cool thing about PowerShell is it’s actually built to work with other hammers. So the reason that you hear me talking about PowerShell all the time is not because I’m replacing my T-SQL Hammer, I’m just automating it! Like this, here’s a quick script to figure out how long your SQL 2008+ instances have been running:

<# Number of Days since the Instance has been restarted #>
$InstanceList = "WIN7NetBook", "WIN7NetBook\R2", "WIN7NetBook\SQLExpress"
foreach($Instance in $InstanceList)
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS 'ServerName'
, DATEDIFF(D, Sqlserver_start_time, SYSDATETIME()) AS 'NumberOfDays'
  FROM sys.dm_os_sys_info" -ServerInstance $Instance -Database master

The automation doesn’t stop with SQL Server. You can find plenty of posts form people in our SQL Community on working with things like Red-Gate’s SQL Compare, Outlook, Active Directory, and Subversion just to name a few. The key here is that all of these people are automating something adjacent to their SQL world and they’re using PowerShell to make it happen.

Heck just last week I wanted to know how many sessions had been submitted to SQL Saturday #67 in Chicago. Do you think I went to the schedule page, copied the list of sessions into Excel and then checked to see what line number the last session was on to get my count?? Heck NO!! Last year I wrote a script for Allen Kinsel ( blog | twitter ) to count how many people at the PASS Summit had registered their twitter handle when the signed up. I took that script, changed the link, changed the term I was looking for and presto! I now know that 105 sessions have been submitted to SQL Saturday #67.

That has nothing to do with SQL Server right?!… Except for that whole bit about it being a SQL Saturday that I was interested in.

<#Start up a web client and download the web page into a text file#>
$webclient = New-Object system.net.webclient
<#Sift through the file for lines that include "viewsession" #>
(Select-String -path $file -Pattern "viewsession").count

OK, so what’s your point?

My point is that I wouldn’t have even had the twitter script that I started with if Allen Kinsel hadn’t taken a chance and sent me an email at noon the Friday before the PASS Summit and asked me if I could use PowerShell to count the number of attendees with twitter handles.

Again, what’s your point?

My point is Use PowerShell to make one tool talk to another.


And tell people faced with an annoying problem that you think I’ve got a script for that because whose knows, maybe I do!

Please Share This:

You may also like:

5 Responses

  1. Thanks for the SQL Saturday script. I’m working on SQL Saturday #75 Columbus and will definitley use the script.

    1. I’m glad that you liked it. I plan to take that idea a little further next week. 🙂

  2. Good post, thanks for playing.

    I use the restart time in TSQL but, unfortunately, that field is only on SQL 2008. I need to research a way to get it from the OS… Do you have a script. 🙂


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner