Picking up a New Hammer

The other day I was answering a question in the forums on PowerGUI.org that I thought would be a quick answer. The only problem was that I had my T-SQL hammer in my hand and not my PowerShell hammer. When I read the question I thought of a way to solve the problem using T-SQL and PowerShell; the only problem was I didn’t think outside of this toolset. PowerShell MVP Shay Levy ( blog | twitter ) chimed in with a much better answer.


Now granted I usually prefer to give answers that your typical data professionals will be comfortable with, ones that pass along a query, but in this case I have to go with the solution that you might not be as comfortable with. There’s a good reason though: It’s faster.

here was my answer:
#Only load this part once when you open up your PowerShell Session
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100            
#Create a text file in your c:\temp\ directory called AllServers.txt
#Put each server\instance on a new line.
foreach ($svr in get-content "C:\temp\AllServers.txt")             
#sp_databases | format-table
invoke-sqlcmd -query "SELECT @@SERVERNAME AS 'ServerName', @@VERSION AS 'SQL Version'" -database master -serverinstance $svr >> "c:\temp\MySQLServerVersions.txt"
and here was Shay’s answer:
$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')            
Get-Content Allservers.txt | Foreach-Object {
   $sql= New-Object Microsoft.SqlServer.Management.Smo.Server $_
   $sql | Select-Object @{n='ServerName';e={$sql.name}},@{n='Version';e={$sql.version}}

Without a doubt Shay’s answer is better because it returned the same results in a better format AND ran in a quarter of the time of my answer. In my environment my solution took a respectable 60 milliseconds to run, Shay’s answer using the SMO took 15 milliseconds. What’s my point today? The SMO is worth learning because it can be faster.�
Where can you learn the SMO? I’m glad you asked 🙂 Take a look at this MSDN article by SMO expert Allen White ( blog | twitter )

del.icio.us Tags: ,,

Please Share This:

You may also like:

One Response

  1. Hi Aaron,

    Just keep in mind that, if you already installed SQL Server 2008, then you already have SMO installed too. So, if you manually load the SQL snapins and providers, you don’t need to load the SMO assemblies. SMO is already part of SQLPS.


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